Drew Boughton
asked on
locking multiple column ranges
Good morning Experts. I am trying to write a VBA code in excel to lock multiple ranges on a worksheet. I can't seem to get it right. As it is right now it is locking down the whole sheet. What have I missed? Any help please. The ranges I have are the ones i want left unlocked on the sheet.
Sub Button1_Click()
Dim i As Integer
Dim User(1 To 3) As String
User(1) = "kahkejianv"
User(2) = "boughtond"
User(3) = "binghamj"
For i = 1 To 3
If Environ("username") = User(i) Then
Worksheets("Master List").Range("Y13:AH256, AK13:AT256").Locked = False
Worksheets("Master List").Protect "pilot320b", AllowFiltering:=True
Exit Sub
Else
End If
Next i
MsgBox "ACCESS DENIED you do not have permission to change sheet"
End Sub
Sub Button1_Click()
Dim i As Integer
Dim User(1 To 3) As String
User(1) = "kahkejianv"
User(2) = "boughtond"
User(3) = "binghamj"
For i = 1 To 3
If Environ("username") = User(i) Then
Worksheets("Master List").Range("Y13:AH256, AK13:AT256").Locked = False
Worksheets("Master List").Protect "pilot320b", AllowFiltering:=True
Exit Sub
Else
End If
Next i
MsgBox "ACCESS DENIED you do not have permission to change sheet"
End Sub
ASKER
Yes I get the same Run-time error 1004.
Try this:
Worksheet.Sheets("Master List").Range("Y13:AH256,AK
Worksheet.Sheets("Master List").Range("Y13:AH256,AK
If multiple ranges doesn't work then split range like below:
Worksheet.Sheets("Master List").Range("Y13:AH256").
Worksheet.Sheets("Master List").Range("AK13:AT256")
Worksheet.Sheets("Master List").Range("Y13:AH256").
Worksheet.Sheets("Master List").Range("AK13:AT256")
Without VBA, if you wanna try, check here
1. First, if the sheet is locked, then unlock it.
2. Select all the sheets cells by clicking in the upper left-hand corner between the "1" and the "A"
3. Right-click->Format->Prote
4. Select the ranges to want to protect and Right-click->Format->Prote
Then for those people for whom you want those ranges locked, do
2. Select all the sheets cells by clicking in the upper left-hand corner between the "1" and the "A"
3. Right-click->Format->Prote
4. Select the ranges to want to protect and Right-click->Format->Prote
Then for those people for whom you want those ranges locked, do
Sheets("Master List").Protect Password:="pilot320b", UserInterfaceOnly:=True, AllowFiltering:=TrueASKER
Thanks guys, I was just told to give it a break and got another project to complete, but i will let you know what works as soon as I get back to it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked perfectly. Thanks for the help. I will have to pick it apart to see how that worked.
You're Welcome Drew! Glad I was able to help.
Worksheets("Master List").unProtect "pilot320b", AllowFiltering:=True
Worksheets("Master List").Range("Y13:AH256, AK13:AT256").Locked = False
Worksheets("Master List").Protect "pilot320b", AllowFiltering:=True