Hi,

I'm trying to create a button that will run two macros:
a) "HideSheets": toggle the visibility of Sheet2 between visible and very hidden
b) "Auto_Close": Runs automatically when the user attempts to close the book. It hides the sheets set in that macro; in this case it’s set to hide Sheet2. The user is then prompted to save the book. The purpose of this is to hide the sheets in case the user forgets to hide them before closing.

I can't get it to work the way I want - I get the password prompt but nothing ever toggles between visible/veryhidden.

Any assistance is appreciated.

Thanks!


The Code:
Sub HideSheets()

' This macro toggles worksheets listed between visible and very hidden
' and requires a password to run
Dim myPassword As String

myPassword = "test" ' Set password here

Password = InputBox("Enter Password")
If Password = "" Then Exit Sub ' Exit if null input or cancel

' Incorrect password
If Password <> myPassword Then
MsgBox Title:="Warning", prompt:="Incorrect Password"
Exit Sub
End If

On Error Resume Next

' Correct password
If Worksheets("Sheet2").Visible = True Then ' Check if one is visable.
' The sheet above is visiable so hide all required
Worksheets("Sheet2").Visible = xlSheetVeryHidden

Else
' Sheets must be hidden, so unhide all required
Worksheets("Sheet2").Visible = True

End If

End Sub

Private Sub Auto_Close()
' This macro hides sheets when the user closes the workbook
' It is a "Private Sub" to prevent it being listed with outher macros

On Error Resume Next

' When workbook is closed hide sheets required
Worksheets("Sheet2").Visible = xlSheetVeryHidden

End Sub