Locking/Unlocking cells based on value in another cell

Anita,

See if this works for you. Right click on your sheet tab, left click on View Code, and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If
End Sub

Any help?

Tom Urtis

Tom -

This code worked PERFECT! Is there any way I could change the message that appears when the cell is locked to something like "Cannot have Promotion and Adjustment at same time"?

Thanks again for your help - this is GREAT!

Anita

Tom,

If I can't change the message, is it possible to change the format of the field to 'shaded' background? I'm going with the "shaded background" is a "non-input" field and "white" background is an "input" field.

Thanks again!

Anita

Anita,

Based on your 2 responses this morning, here is a solution that I think kills both birds with one stone. Instead of locking and protecting the sheet, we just tell Excel that when B3 = Promotion, then to color cell S3 a light shade of blue, and delete the contents in S3. If anyone tries to click on, arrow over to, or otherwise select cell S3, a friendly message will pop up, telling them they cannot access S3 because of the Promotion. If they hit OK and still try to enter something in S3, this code will automatically delete that attempt.

IMPORTANT !!
Please entirely delete the code I sent you previously, and replace it with these two worksheet level events. Again, right click on the sheet tab, left click on View Code, and paste these in there instead, all at once is fine.

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
[S3].Interior.ColorIndex = 34
[S3].ClearContents
Else
[S3].Interior.ColorIndex = 0
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$S$3" Then
If [B3] = "Promotion" Then
[S3].ClearContents
MsgBox "Sorry, cannot have Promotions and" & vbCrLf & _
"Adjustments at the same time", 64, "Access into cell S3 not allowed."
End If
End If
End Sub

Hope all your Promotions are happy ones !!
Have a nice weekend.

Tom Urtis

: I was wondering if this can even be done and if so...how????

Tom,

I do need to lock and protect the sheet for a variety of other cells, will this create a problem?

Thanks again for all of your help!!!

Anita

, Based on your 2 responses this morning, here is a solution that I think kills both birds with one stone. Instead of locking and protecting the sheet, we just tell Excel that when B3 = Promotion, then to color cell S3 a light shade of blue, and delete the contents in S3. If anyone tries to click on, arrow over to, or otherwise select cell S3, a friendly message will pop up, telling them they cannot access S3 because of the Promotion. If they hit OK and still try to enter something in S3, this code will automatically delete that attempt. IMPORTANT !!

There is slight overkill in the code, in case someone tinkers with the sheet while it is unprotected and activates the Locked property of S3, which would lead to a run time error next time someone deletes "Promotion" from B3. Amended code, note the 3rd-to-last line in the Change event is optional:

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = False
[S3].Interior.ColorIndex = 34
[S3].ClearContents
ActiveSheet.Protect ("PASSWORD")
Else
ActiveSheet.Unprotect ("PASSWORD")
[S3].Interior.ColorIndex = 0
'Next line is optional, remove preceding apostrophe if protection should stay on.
'ActiveSheet.Protect ("PASSWORD")
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$S$3" Then
If [B3] = "Promotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = False
[S3].ClearContents
ActiveSheet.Protect ("PASSWORD")
MsgBox "Sorry, cannot have Promotions and" & vbCrLf & _
"Adjustments at the same time", 64, "Access into cell S3 not allowed."
End If
End If
End Sub

Tom Urtis

Tom,

I have added the code that you recommended but I don't know if this makes a difference or not. The sheet is protected but there is also the "share and protect workbook" protection that is on as well. I don't know if this changes the code then or what but I am getting a message that says "Unprotect method of Worksheet class failed". I'm thinking this might be because it is protected twice essentially, once for the form contents and such and again for shared workbooks. Also, will this code work for all rows in my spreadsheet even if I'm referencing cell B3 and S3 in the code? I know next to nothing about VB so this is a good learning experience for me. Thanks so much for all of your help with this!

Anita I was wondering if this can even be done and if so...how????

Tom,

I would like to use this code for cells B3 through B51 and S3 through S51 - I don't know the "syntax" of changing this to a "range" of cells. Can you help?

Thank you!
Anita There is slight overkill in the code, in case someone tinkers with the sheet while it is unprotected and activates the Locked property of S3, which would lead to a run time error next time someone deletes "Promotion" from B3. Amended code, note the 3rd-to-last line in the Change event is optional: