Formula Protection while allowing users to modify value in cells?

[jinx2311]

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?

 

[Clyde38]

jinx2311,
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.

 

[joerd]

Usually, you want to have input cells not protected (= not locked, in XL speak), and calculated cells/formulas protected. So, before you apply protection to the sheet/workbook, select the input cells, for which you'll allow changes by the user, and format them "not locked". Then, apply sheet protection and workbook protection.
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.

 

[jinx2311]

Ok heres the thing...The input cells where I want the users to input/select values, have IF functions in them which would decide which options to display depending on some other cell. If I select that cell and say "allow user to modify" my IF condition is being wiped out and I dont get the desired result if I want to re-input different values.

HELLPPPPPP!

 

[Clyde38]

Well of course you can't have a user input data to a formula cell without expecting the cell to change. I would suggest that you have your formula "if statements etc." reference another cell and make that cell a user input with the proper protection/format applied to the worksheet and individual cells.

 

[joerd]

You might want to look into Data Validation, to display a little list box with allowed values. If you set up a named range that contains the appropriate values to list, you can protect the list while still allowing the input cell to be modified.
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.

 

[jinx2311]

Thanks for the info Clyde38! I will try to reference it to some other cell.

Joerd:I have already done all that, but you just confirmed that I am on the right track!Thanks!

 

[psafety]

Jinx2311,
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