Password Protect & Hide Worksheets in Excel Video - Add custom password?

Hi Excelerate and welcome to the Forum.

One way to allow a user to specifiy a password or none (without hard-coding it into the procedures) is to store the password which is invisible to the user (assuming they have no knowledge or VBA or you have protected your VBA project).

In the attached file, there's a VeryHidden worksheet called "User Data" in which cell B2 stores a passord (="123456" as it leaves me). You could name that cell of course.

Then the declaration: 

Public Const conSheetPassword As String

is changed (so it's not a Constant but a Public string varaible), as in bold below plus a the changes in bold below to the start of this sub:

Public conSheetPassword As String


Sub ViewSheet()
' Code from: https://www.TeachExcel.com
'
' Unlock/show the worksheet if the user inputs the correct password.

    ' Variables.
    
    
    Dim userInput As Variant
    'get the user's password (if any)
    conSheetPassword = Sheets("UserData").Range("B2").Value

    ' Ask the user for the password to unlock the sheet.
    If Len(conSheetPassword) > 0 Then
        userInput = InputBox( _
            Prompt:="Input a password to unlock the worksheets.", _
            Title:="Password Input", _
            Default:="Password")
        Else
        userInput = ""
    End If

(which checks if there's a password or not and asks for it if so).  The rest of that sub is unchanged.

The end of the HideSheet macro then needs changing as follow (changes in bold)

Sub HideSheet()
' Code from: https://www.TeachExcel.com
'
'...< <<unchanged code not shown>>>
    ' check if the user wants to add or change the password
    StorePassword

    ' Hide the sheets so the user can't unhide it by hand (so long as the Project
    ' code here is also password protected).

    Sheets("Helper").Visible = xlSheetVeryHidden
    Sheets("Raw").Visible = xlSheetVeryHidden
    ' make sheet including password hidden too
    Sheets("UserData").Visible = xlSheetVeryHidden
End Sub

where it calls a new sub:

Sub StorePassword()

Dim Ans As String
' Ask if a new password is needed (where the argument returns a strring)
Ans = InputBox("Want to enter a NEW password? (Press Cancel to keep old)", "Set NEW password (if needed)or clear", "Password", 2)
' if it was changed, store it in hidden sheet
If Ans <> "Password" And Ans <> conSheetPassword Then Sheets("UserData").Range("B2").Value = Ans

End Sub

which displays a simple InputBox where the password can be kept (using Cancel), changed by typing something or no password set (by deleting the default text Password)

At the Workbook level, I added two event macros to ensure the file is saved with sheets hidden (in case it is later opened with macros disabled) and sets the variable when the workbook is opened.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Hide sheets (in case file can be re-opened without macros enabled)
Sheets("Helper").Visible = xlSheetVeryHidden
Sheets("Raw").Visible = xlSheetVeryHidden
Sheets("UserData").Visible = xlSheetVeryHidden

End Sub

Private Sub Workbook_Open()

conSheetPassword = Sheets("UserData").Range("B2").Value

End Sub

Hope this fixes your problem.