VBA Toggle Button on Protected Sheet

[The_Steward]

I am running this Macro to hide/unhide rows when form control button is clicked.

Sub Important_Info_TOGGLE()
    Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
End Sub

However when I protect the sheet it does not work. How can I fix this Macro so it runs when sheet is protected?

I had thought of using something like below but unsure how to configure correctly or if this would work.

If Intersect(Target, OLEObject("CommandButton4")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:=""
    With Sheets("Sheet19")
       Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
    End With
    ActiveSheet.Protect Password:=""

 

[Jamie McMillan]

Hello,

Your thinking is right.

The code below works - if the button is on the active worksheet.

Sub HideRows()
ActiveSheet.UnProtect "PassWord"
Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
ActiveSheet.Protect "PassWord"
End Sub

If you have several worksheets to unprotect; then protect: - and hide the rows, the below code does that.

Sub UnProtect()
Dim ws As Worksheet
For Each ws In Worksheets
ws.UnProtect "PassWord"
Next ws
Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
For Each ws In Worksheets
ws.Protect "PassWord"
Next ws
End Sub

Jamie

 

[The_Steward]

Hello,

Your thinking is right.

The code below works - if the button is on the active worksheet.

Sub HideRows()
ActiveSheet.UnProtect "PassWord"
Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
ActiveSheet.Protect "PassWord"
End Sub

If you have several worksheets to unprotect; then protect: - and hide the rows, the below code does that.

Sub UnProtect()
Dim ws As Worksheet
For Each ws In Worksheets
ws.UnProtect "PassWord"
Next ws
Rows("24:42").EntireRow.Hidden = Not Rows("24:42").EntireRow.Hidden
For Each ws In Worksheets
ws.Protect "PassWord"
Next ws
End Sub

Jamie

Thankyou Jamie! You are amazing :D

Didn't think it would be that simple,

Worked like a charm.