Formula Protection while allowing users to modify value in cells?
Formula Protection while allowing users to modify value in cells?
(OP)
Hi All,
I am having a problem with cell modifications by users for my application in Excel 2000. I cannot protect my formulae in the cells without protecting the worksheet and protecting (locking,hiding) the cell itself and in that case, it then wouldnt let any user enter any value or even select any value from the dropdown list provided for that cell.It says "File Read-Only". How do I protect my formula while allowing the users to change values in the cells?
I think the answer lies somewhere in the protection option, and I have been trying to do it in different ways, but maybe I am doing it in an incorrect sequence or so....Could someone please help me on it?If possible give me the sequence of steps, in case I am not doing it correctly?
I am having a problem with cell modifications by users for my application in Excel 2000. I cannot protect my formulae in the cells without protecting the worksheet and protecting (locking,hiding) the cell itself and in that case, it then wouldnt let any user enter any value or even select any value from the dropdown list provided for that cell.It says "File Read-Only". How do I protect my formula while allowing the users to change values in the cells?
I think the answer lies somewhere in the protection option, and I have been trying to do it in different ways, but maybe I am doing it in an incorrect sequence or so....Could someone please help me on it?If possible give me the sequence of steps, in case I am not doing it correctly?





RE: Formula Protection while allowing users to modify value in cells?
Before you protect your worksheet, select the cells that you want the user to access "format cells" and uncheck the locked and hidden boxes. When you protect the worksheet, select the options that you want from the list "allow all users of the worksheet to" such as "select unlocked cells". I hope this helps.
RE: Formula Protection while allowing users to modify value in cells?
It usually is a good idea to format the input cells in a consistent way, say with a blue font, so the user can easily see where her input should go.
I don't know how you'll get a file read-only error when you try to make changes to a cell. Something like this only occurs when you try to save a file which was opened as read only. If you give me some more clues, I might be able to help.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Formula Protection while allowing users to modify value in cells?
HELLPPPPPP!
RE: Formula Protection while allowing users to modify value in cells?
RE: Formula Protection while allowing users to modify value in cells?
Example:
A1 is your input cell. Set up Data Validation to allow a list of values: =MyList
Define a name (Ctrl-F3) called MyList that refers to, for example, cells C1:C6
In C1 through C6 you write your IF statement(s), dependent on the value of cell B1:
C1: =IF(B1=1, "a","b")
C2: =IF(B1=1, "c","d")
etc.
You may also use CHOOSE, or VLOOKUP functions etc, to achieve the list of values that you want.
Happy exploring!
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Formula Protection while allowing users to modify value in cells?
Joerd:I have already done all that, but you just confirmed that I am on the right track!Thanks!
RE: Formula Protection while allowing users to modify value in cells?
In one of my spreadsheets I needed some thing similar. I built the following macro and placed in "This Worksheet" VBA code.
This is for a compressible fluid flow worksheet in which cell F8 is the specific volume of a gas. Of course I need it unprotected in the event the calc is for a gas not include in my lookup table. D134 is the specific volume for steam (being different from other gases). Cell C6 contains the gas name and A110 is steam in the lookup table (for other varibles).
Basically, the user can input anything in F8, but if he selects it again this triggers the following macro.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.AddressLocal = "$F$8" Then
If Len(Target.Text) = "$D$134" Then
Exit Sub
Else
Target.Formula = "=IF(C6=A110,D134,10.73*F11/(F7*F9))"
End If
End If
End Sub