Running a Macro with a Protected Sheet

[excellingatmyjob]

Hello,

I have a macro running to hide/unhide certain columns depending on a user's selection in the drop down menu(s). I then unlocked the drop-down menu cells and protected the sheet. When I do so, the following error shows up:

Run-time error '1004":
Unable to set the Hidden property of the Range class

I'm pretty sure this error comes up because the sheet is protected, but how can I make it so the user can select ONLY the drop-down menu cells, yet the macro will still run? Below is my current code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E29") = "*None" Then
Rows("30:31").EntireRow.Hidden = True
Rows("33").EntireRow.Hidden = False
ElseIf Range("E29") = "Perimeter Handrails Only" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Flush Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Offset Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Other" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
End If
End Sub

Any assistance will be greatly appreciated!
Thank you.

 

[bbott]

Try it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E29") = "*None" Then
[B][COLOR=#ff0000]ActiveSheet.Unprotect
[/COLOR][/B]Rows("30:31").EntireRow.Hidden = True
Rows("33").EntireRow.Hidden = False
ElseIf Range("E29") = "Perimeter Handrails Only" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Flush Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Offset Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Other" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
End If
[B][COLOR=#ff0000]ActiveSheet.Protect
[/COLOR][/B]End Sub

 

[excellingatmyjob]

That prompts me to enter in the password...

The users will not have the password because I do not want them to edit other cells. Anyway to do this without a password prompt?

 

[bbott]

Just put your password in the red areas in the amended code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E29") = "*None" Then
ActiveSheet.Unprotect Password:="[B][COLOR=#ff0000]here[/COLOR][/B]"
Rows("30:31").EntireRow.Hidden = True
Rows("33").EntireRow.Hidden = False
ElseIf Range("E29") = "Perimeter Handrails Only" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Flush Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Offset Platform one Face" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
ElseIf Range("E29") = "Other" Then
Rows("33").EntireRow.Hidden = True
Rows("30:31").EntireRow.Hidden = False
End If
ActiveSheet.Protect Password:="[B][COLOR=#ff0000]here[/COLOR][/B]"
End Sub

 

[excellingatmyjob]

That worked - I just had to put that line - ActiveSheet.Unprotect Password:="bac" - after every ElseIf

Thank you so much!

 

[bbott]

You're welcome.