vba to lock columns b thru d based on value selected in col a

  • Thread starter Thread starter cm
  • Start date Start date
C

cm

This code needs to apply to one row at a time.

Column A - each cell has data validation -- possible values of Yes and No.

If the user chooses 'Yes' in A1, I need to unlock B1 thru D1 (and keep them
that way), otherwise those cells should be locked.

Same thing on row 2, etc, down to possible 300 rows or more.
 
Assumptions

1. Columns B:D are locked.

2. Column A is unlocked.

3. Sheet protected with password of "justme" no quotes.

4. User can go back and change "Yes" to "No" which would re-lock the B:D
cells.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
Me.Unprotect Password:="justme"
n = Target.Row
If Me.Range("A" & n).Value = "Yes" Then
Me.Range("B" & n & ":D" & n).Locked = False
Else
Me.Range("B" & n & ":D" & n).Locked = True
End If
End If
enditall:
Application.EnableEvents = True
Me.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP
 
Back
Top