Locking cells based on the value of other cells

0

I'm trying to edit a pricing calculator spreadsheet for a friend.

There are numerous options to choose from, I am trying to work out an IF function code that will enable me to lock a cell if another cell is choosen but still allow me to choose that cell if a different option is choosen without loosing my formula.

For example there are 3 options for widths if I choose the 3rd option I can't have a certain depth but if I choose either of the first 2 options I can have that depth or another 4 other options.

It doesn't seem to be the straight forward IF statement I am used to creating. As it's there are 3 options not 2

=IF(c11=1,"0","")

I'm hoping someone can understand this and help me out a little.

Thanks in advance

Post Edited
Title: Title was not descriptive.
Answer
Discuss

Answers

0

check out Nesting If statements....or  maybe even Select Case Statements

Discuss
0

You need a macro for this.

Hit Alt+F11 and insert this macro into the Sheet module where the selections are going to be made. This macro below assumes that it is on Sheet1.

Here is a macro that should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Address = "$A$2" Then Exit Sub

If Target.Value = "c" Then

    'unprotect the sheet
    Sheets("Sheet1").Unprotect

    'protect the cells so they can't be changed
    Range("B2").Locked = True
    Range("C2").Locked = True
    Range("D2").Locked = True

    'protect the worksheet so the cells can't be changed
    Sheets("Sheet1").Protect

Else

    'unprotect the sheet
    Sheets("Sheet1").Unprotect

    'protect the cells so they can't be changed
    Range("B2").Locked = False
    Range("C2").Locked = False
    Range("D2").Locked = False

    'protect the worksheet so the cells can't be changed
    Sheets("Sheet1").Protect

End If


End Sub

$A$2 is the address of the cell where the selection will be made. Dollar signs must go infront of the letter and number of the cell address. You must also unlock this cell to begin with by hand. Right-click > Format Cells > Protection tab > uncheck Locked and hit OK.

Sheet1 is the name of the sheet where the selection will be made.

B2 C2 and D2 are the cells where the other options should be located.

c is the value that cell A2 should have to cause the other options to not be able to be selected.

Notes:

There is currently no password required to lock or unlock the sheet.

Also, you must unlock any other cells on the worksheet that you want to be able to be changed or accessed once the worksheet has been protected. Right-click > Format Cells > Protection tab > uncheck Locked and hit OK.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login