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


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
 
Ad

Advertisements

Joined
Jul 20, 2011
Messages
16
Reaction score
0
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
 

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