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

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.
 
G

Gord Dibben

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
 

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

Top