×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Formula Protection while allowing users to modify value in cells?

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?

RE: Formula Protection while allowing users to modify value in cells?

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.

RE: Formula Protection while allowing users to modify value in cells?

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.

RE: Formula Protection while allowing users to modify value in cells?

(OP)
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!

RE: Formula Protection while allowing users to modify value in cells?

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.

RE: Formula Protection while allowing users to modify value in cells?

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.

RE: Formula Protection while allowing users to modify value in cells?

(OP)
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!

RE: Formula Protection while allowing users to modify value in cells?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources