Excel Excel Macro to protect cells in row if a certain word is selected

T

Timbo2000

Joined
Jul 8, 2011
Messages
1
Reaction score
0
I have a worksheet where column A uses data validation, from row 1 to row 50, to allow the user to select from two options. I need a macro that, when option A is seelected in say cell A1, cells B1, C1, D1, E1 and F1 will be protected and if Option B is selected it will unlock them again. I need it to be able to lock the cells in the row depending which row the Option is selected in.
I have been trying something with an event change but I can only get it to lock for one one row.
Hope this make sense.

Thanks,

Timbo2000
 
Try out the sample Code below:

Code: 
Sub Macro1()
'
' Macro1 Macro
'

Dim wrkSheet As Worksheet
Dim xrow As Long, xcol As Long
Dim opt

ActiveSheet.Unprotect

Set wrkSheet = ActiveSheet
Range("A1:A100").Select
Selection.Locked = False
Range("A1").Select

xrow = ActiveCell.Row
xcol = ActiveCell.Column
opt = Range("A" & xrow).Value

If opt = "B" Then
    Range("B" & xrow & ":" & "F" & xrow).Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
Else
    Range("B" & xrow & ":" & "F" & xrow).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True    
End If
End Sub
 
Back
Top