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



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.



Gord Dibben


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

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
Me.Range("B" & n & ":D" & n).Locked = True
End If
End If
Application.EnableEvents = True
Me.Protect Password:="justme"
End Sub

Gord Dibben MS Excel MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question