Auto hide & protect worksheets before closing

Hi folks,

I wrote the following code to hide & password protect an excel sheet :

Sub Hideconfidential()
Worksheets("confidential").Visible = xlSheetVeryHidden
End Sub
Sub UnHideconfidential()
rspn = InputBox("Enter Password")
If rspn <> "Pw" Then Exit Sub
Worksheets("confidential").Visible = xlSheetVisible
End Sub

How could I alter it so that it hides and protects this sheet automatically before closing the workbook ?

Thank you!

Hi magnum206

in the workbook module place

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("confidential").Visible = xlSheetVeryHidden
Me.Protect ("pike")
End Sub

Originally Posted by intervals

I am trying to implement this in a spreadsheet I have and I cannot get the code to work.

I have 5 sheets
I want sheet "Info" to remain visible
But sheets, times, Email, Print, Run Comp, Leaderboard to hide when sheet is closed.

Firstly, You will need to FORCE users to enable macros.

Multiple versions of 'Force Enable Macros' code exists (just google). Here's one: http://xl-central.com/force-users-to...-workbook.html

After implementing code to Force users to enable macros, you will need to modify it to suit your requirements.

So using the example given at xl-central,

Private Sub HideAllSheets()
Const strWSInfo as string = "Info"
Const strWSConfidential as string = "confidential"

'N.B. I don't recommend you rely on hardcoded tab names to identify specific sheets.
' Because they only work as long as nobody renames the tab
' Learn to use sheet code names instead

Const strPwdConfidential as string = "Pw"

    'Declare the variable
    Dim Sh As Object
    
    'Display the warning sheet
    Sheets(Warning).Visible = xlSheetVisible
    
    'Hide every sheet, except the warning sheet
    For Each Sh In Sheets
        If Sh.Name <> Warning And Sh.Name <> strWSInfo Then
            Sh.Visible = xlSheetVeryHidden
            If Sh.Name = strWSConfidential  Then: Sh.Protect strPwdConfidential  
        End If
    Next Sh
   
End Sub