How to VeryHidden worksheets and display them with a password

[K0st4din]

Hello, everyone,
I love this excel so much that he can't put it into words.
Macros, I adore them.
The truth is that I searched in many sites, but very rarely there is a macro that is 100% consistent with what the user needs.
I could not find for my needs and therefore I ask you for help.
Here's what I need:
I have a workbook with 50 worksheets with different names.
I'm looking for a macro that always leaves a worksheet with a name (base) and the others with names ("peaches", "bananas", etc.) to be VeryHidden after the workbook closes (maybe Me.Save) .
And when I open the workbook, only the worksheet (base) is visible and I can show all the hidden worksheets with a password.
If the password is incorrect, do not show any of the hidden worksheets
I will be grateful for any help.
Greetings

 

[Leandroarb]

Hi,
See this example:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'On workbook code, before close
Sheets(Array("Plan2", "Plan3")).Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWorkbook.Save
End Sub

'/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/=/

'On a buttoncommand
Sub Botão1_Clique()
If InputBox("Password") <> 123 Then
    MsgBox "Incorrect pass, try again!"
    Else
    Sheets("Base").Select
    Sheets("Plan2").Visible = True
    Sheets("Plan2").Select
    Sheets("Plan3").Visible = True
End If
End Sub

 

[mikerickson]

Something like this perhaps. You might want to use code names of the workbooks rather than the names of the workbooks, so that the code wont crash if the user renames the Base worksheet

' in ThisWorkbook code module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oneSheet As Worksheet
    With Me
        .Worksheets("Base").Visible = xlSheetVisible
        For Each oneSheet In .Sheets
            If oneSheet.Name <> "Base" Then
                oneSheet.Visible = xlSheetVeryHidden
            End If
        Next oneSheet
    End With
End Sub


Private Sub Workbook_Open()
    Dim uiPassword
    Dim oneSheet As Worksheet
    Const soughtPassword As String = "password"
    uiPassword = Application.InputBox("Enter password", Default:=soughtPassword, Type:=2)
    If uiPassword = soughtPassword Then
        For Each oneSheet In Me.Sheets
            oneSheet.Visible = xlSheetVeryHidden
        Next oneSheet
    Else
        MsgBox "wrong password"
    End If
End Sub

 

[K0st4din]

mikerickson

In the same module, do I put both macros or in separate ones?
I will try it and give back an answer, what happened!
Thank you very much!

 

[MARK858]

As Mike put at the top of the code, you put them both in the ThisWorkbook module ( Find your workbook in the VBA Project window, look down and you will see ThisWorkbook, right click and then click view code. Your codes both go in the window that appears).

' in ThisWorkbook code module

 

[K0st4din]

Something like this perhaps. You might want to use code names of the workbooks rather than the names of the workbooks, so that the code wont crash if the user renames the Base worksheet

' in ThisWorkbook code module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oneSheet As Worksheet
    With Me
        .Worksheets("Base").Visible = xlSheetVisible
        For Each oneSheet In .Sheets
            If oneSheet.Name <> "Base" Then
                oneSheet.Visible = xlSheetVeryHidden
            End If
        Next oneSheet
    End With
End Sub


Private Sub Workbook_Open()
    Dim uiPassword
    Dim oneSheet As Worksheet
    Const soughtPassword As String = "password"
    uiPassword = Application.InputBox("Enter password", Default:=soughtPassword, Type:=2)
    If uiPassword = soughtPassword Then
        For Each oneSheet In Me.Sheets
            oneSheet.Visible = xlSheetVeryHidden
        Next oneSheet
    Else
        MsgBox "wrong password"
    End If
End Sub

Hi, I tried the macro, but something needs to be touched.
I changed the name of the worksheet to mine.
When closed, all worksheets are hidden, except for worksheet 1 (as it should be).
But when opened, the window in which I have to write the password is displayed, and in this case, it is written by itself.
The typewriter window should be blank and I should write the password.
And for the finale, it shows me some mistake.
Ask for assistance.
Thanks in advance.

 

[K0st4din]

 

[mikerickson]

I adjusted this to use the code name of the master sheet. And I corrected the variable to xlSheetVisible.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oneSheet As Worksheet

    Sheet1.Visible = xlSheetVisible
    
     For Each oneSheet In Me.Sheets
         If oneSheet.Name <> Sheet1.Name Then
             oneSheet.Visible = xlSheetVeryHidden
         End If
     Next oneSheet
     
End Sub

Private Sub Workbook_Open()
    Dim uiPassword
    Dim oneSheet As Worksheet
    
    Const soughtPassword As String = "password"
    uiPassword = Application.InputBox("Enter password", Default:=soughtPassword, Type:=2)
    
    If uiPassword = soughtPassword Then
        For Each oneSheet In Me.Sheets
            oneSheet.Visible = xlSheetVisible
        Next oneSheet
    Else
        MsgBox "wrong password"
    End If
End Sub

 

[HappierThan]

When I first learned how to make use of 'Hidden Sheets' I thought it was marvelous until. . . . The many and varied way you can get into the 'Hidden Sheets' made the whole exercise a total waste. Plus if you were to do a Move or Copy sheets, the Hidden Sheets were lost. I have found the use of well hidden "Helper" columns within the worksheet the best alternative. Best of luck and stay well.

 

[mikerickson]

Hidden sheets are a good place to store static data, out of sight and out of the way.
They are a lousy way to keep secrets.