Excel: Locking/unlocking cells based on value in another cell

[JCGSTL]

I have a spreadsheet that is protected, leaving only specific cells unlocked for users to edit.

I want to lock a range within the sheet if the value of B7 is greater than 34.

Based upon thread68-1318817: Locking cells with VBA I tried the following routine:

Private Sub Small_Group_supplement()
If [B7] > 34 Then
Range("b42:i42").Locked = True
Else
Range("b42:i42").Locked = False

End If
End Sub

When I protect the sheet again nothing appears to happen. I assume I am missing something. Can anyone enlighten me?

Many thanks in advance.

 

[stefen054]

Hi

you have to use worksheet_selectionchange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Small_Group_supplement(Target.Parent)
End Sub

and the locking code could be as follows
Sub Small_Group_supplement(ByRef sh As Worksheet)
With sh
.Unprotect "Password"
.Cells.Locked = False
If .[B7] > 34 Then
.Range("b42:i42").Locked = True
Else
.Range("b42:i42").Locked = False
End If
.Protect "Password"
End With
End Sub


Thanks
Stefen

 

[JCGSTL]

Stefen

Excellent! Worked like a charm!!

Many thanks for your help.

John