Unable to Hide/Unhide Macro because of Workbook structure protection

[albert2]

Hello Excel expert!

I came up with this group because I can find my solution through google. I have a workbook-protected STRUCTURE and I also have a macro that hides and unhides one worksheet.
When I run the macro to hide/unhide a sheet an error pop up. I hope you could help me with this. below is the screenshot for your reference. below is my hide/unhide code.

Sub Unhide_SD_Sheet()

If Sheet2.Visible Then Sheet2.Visible = xlSheetHidden Else: Sheet2.Visible = xlSheetVisible

If Sheet2.Visible Then MsgBox ("Supporting Documentation Sheet Now Visible") Else MsgBox ("Supporting Documentation Sheet Now Hidden")


End Sub

 

[Fluff]

You will need to unprotect the workbook before trying to (un)hide the sheet & then reprotect it afterwards.

 

[albert2]

You will need to unprotect the workbook before trying to (un)hide the sheet & then reprotect it afterwards

I want my workbook to be protected. Is there a way of running macro even though it's protected?

 

[Fluff]

Yes, as I said you unprotect & reprotect within the code.

 

[Joe4]

I want my workbook to be protected. Is there a way of running macro even though it's protected?

As Fluff said, you need to unprotect it first.
If you were able to run it without doing that, the protection would pretty much be worthless, right?

 

[albert2]

As Fluff said, you need to unprotect it first.
If you were able to run it without doing that, the protection would pretty much be worthless, right?

I have 35 sheets in 1 workbook and they are all protected. Now, when I run the codes (hide/unhide) that the error comes out. I need to keep it protected for the rest of the sheets.

 

[Fluff]

Try it like

Sub Unhide_SD_Sheet()
ThisWorkbook.Unprotect "Password"
If Sheet2.Visible Then Sheet2.Visible = xlSheetHidden Else: Sheet2.Visible = xlSheetVisible

If Sheet2.Visible Then MsgBox ("Supporting Documentation Sheet Now Visible") Else MsgBox ("Supporting Documentation Sheet Now Hidden")
ThisWorkbook.Protect "Password"

End Sub

 

[albert2]

Try it like

Sub Unhide_SD_Sheet()
ThisWorkbook.Unprotect "Password"
If Sheet2.Visible Then Sheet2.Visible = xlSheetHidden Else: Sheet2.Visible = xlSheetVisible

If Sheet2.Visible Then MsgBox ("Supporting Documentation Sheet Now Visible") Else MsgBox ("Supporting Documentation Sheet Now Hidden")
ThisWorkbook.Protect "Password"

End Sub

Oh wow, it worked. I never expect it's gonna be that simple. Thank you so much I appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.