way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).
View 14 RepliesI try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
I have got the task of doing the results for a local athletics mid-distance event once again and im fed up of doing it the present way. Basically i have a sheet which i input the race registrations which has the following collums: Number, Name,School,
and i have another sheet for results which have: Position,Number,Time. I then have to merge the two in a magical way and i get the results. However what i want to do is have the first sheet the same but the results sheet would be different reading: Postion,Number,Name,School, Time
But when i input the number, it will check with the other sheet and automatically fill the rest of the boxes (bar position and time) hopefully. Using my knowlegde i gathered at college(fairly average knowledge of vb) i still cant get it to work the way i planned. Is it actually possible to do, or should i go to my pet hate access?
I have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
View 1 Replies View Related1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
I have a bunch of data (by date) on one worksheet and I want to output results to another worksheet (in the same workbook). I want to be able to alter the rolling period for some financial calculations (thus the rolling period countdown). The code I've written doesn't suck the data from the second worksheet. The first worksheet is where vba is launched from. I've attached all the code.
Private Sub CommandButton1_Click()
Dim rowworking As Integer
Dim rowoutput As Integer
Dim rollingperiodcountdown As Integer
Dim Date1 As Date
Dim expectedreturn As Single
Dim returnwithedc As Single
Dim trackingdifference As Single
Dim returnwithbetamodulation As Single
Dim betamodulationtrackingdifference As Single
Dim improvement As Single
Dim x As Integer
Range("a5:g15000").Select
Selection.ClearContents
Range("a5").Select
For rowoutput = 5 To 500
Worksheets("Rolling Period").Activate
For rowworking = 3 To 15000
rollingperiodcountdown = Cells(rowworking, 9).....................
Basicalley I have a series of columns, each 5 letters together represent an order(a,b,c,d,e), the columns are repeated approximately 15 times, similar to the following;
Bag Size:B/G5lb2lb 16oz8ozB/G 5lb 2lb (repeat...)
Catagory 1ABCDEA1 B1 C1 (repeat...)
Catagory 2 AABBCCDDEEAA1 BB1 CC1 (repeat...)
(...Repeat)
The B/G stands for Whole Bean, or Ground, the A's are drop down lists giving those 2 options, the other cells (non-A's) are all numeric. At the end of each row, the orders need to be summed up into categories; 5lb whole, 5lb ground, 2lbs whole, 2lbs ground, 16 whole, 16 ground etc..
I am looking for a way to sum each category for all orders as necessary, in one cell adding all B's (in that row for that catagory) if its corresponding A is whole bean, in the next summing all b's if its corresponding A is Ground. The same will go for the C's D's and E's.
How can i protect the particular cells
Ex.
All the column g
we are using a shared excel file in a network. Is there a way to protect cells/columns from updating by other users, and i can only update them.
View 9 Replies View RelatedI know how to protect a sheet and unlock some cells, but how can I lock the format of ALL cells while still allowing users to enter information? Ifve been instructing people to Paste Special  Values, but of course itfs more difficult. For example, Ifd like to protect a sheet but allow cells B2:C2 to be editable in value only – NOT in format. I tried searching the forum for a solution but was unable to find anything.
View 2 Replies View Relatedhow do i protect a cell from being changed? I don't want to protect the whole worksheet, just a few cells within a worksheet? I have tried the Allow Range Users function, but cannot get it to work? Kind regards, Michele
View 8 Replies View RelatedI am running a loop that changes the value in one cell at a time. Since this is for our entire company, I am trying to make it "idiot-proof" like most of you have done.
While in the loop, I want to unprotect a cell, change the value, then reprotect the cell. But the rest of the worksheet is going to be unprotected for now. is that possible? Or do I have to protect the whole sheet, and then .Unprotect the whole sheet first and then. Protect it at the end of the macro.
I would like to protect some cells of a spreadsheet, without using Excel's protection, because I only want to prevent the deletion of those cells - the cells must remain editable.
View 4 Replies View RelatedI have compiled a spreadsheet for my co-workers with basic formulas. I would like to know how to protect the formula from being accidently deleted. My spreadsheet now will calculate what is needed but if someone accidently clicks on the cell containing the formula and clears or backspaces it erases this formula. I'm sure there is a way to prevent this.
View 2 Replies View RelatedHere's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
View 14 Replies View RelatedI was wondering if it was possible to hide cells using protect sheet? I have a worksheet that contains confidential data and would like it so the cells appeared blank unless you had the password. I know you can hide rows and columns but I need everything on the sheet to be hidden.
View 2 Replies View RelatedIs there any possibility to protect alternate cells of column see below for example. I want to lock cells B1, B6, B11 and B16 only so that no one is able to delete the average rate.
A B
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
Can I protect cells so that people can't change them, but at the same time they are able to delete rows and columns?
If I remove the locking and use hide only then the cell why go empty if someone double cliks it.
How to protect a single or multiple cell in excel.
View 1 Replies View RelatedI Need to protect some cells from being Changed or deleted, if i protect the whole sheet there are some cells that need to be changed using the formatting bar I.e borders, colours, font- colour, Merged cells.
the protection setting allows you to leave individual cells unprotected however you still cannot change the border for example.
I have a grid used as a shared diary and I need to automatically protect a cell by changing its colour to red, can this be done using VBA?
View 9 Replies View RelatedIn the attached example, I have two fields. In the first (top), I have fields I want the user to be able to enter data (revenue/weight/skids). The information at the bottom is generated by formulas that act on the user-provided data and display the results at the bottom. The key, though, is keeping Excel-ignorant users from inadvertantly destroying the formulas and data in the middle section. I still need to display the results at each step, though, so hiding those middle fields is not an option. How can I lock only some cells within a single spreadsheet like this?
View 2 Replies View RelatedIam making some test report tamplet`s, in that when I select the material from drop down (ref enclosed xls file) all spec will be displayed. ·I want the sheet to be protected at the same time few cells to be allowed for data entry (cells marked with blue)· If sheet can`t be protected I want to hide the formulas and spec details used in it.
View 2 Replies View RelatedHow do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
View 5 Replies View RelatedHave a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work.
How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
How to protect only the colored cells in excel sheet which has formulas in it and it is a sale sheet because i do not want my team to mixup with the formula.
View 9 Replies View RelatedI build a lot of spreadsheets at work that I email out and the person I send them too has to enter information in the cells highlighted in yellow. Since not many of the end users are too computer savvy I lock and protect all the cells that they shouldn't be changing.
This can take forever, is there any way to have all the cells in a worksheet become locked and protected based on the formatting they have? All cells that aren't yellow?
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.
View 5 Replies View Related