Password protection for worksheet

Paste the code below to the ThisWorkbook code module in your project. Follow the instructions at the top of the code to name your worksheets to match the code. You must identify the sheet to be hidden and designate another one to be visible when the workbook opens. Then save the workbook as macro-enabled (xlsm format) and close it.

Private Sub Workbook_Open()
    ' Variatus @Teach Excel
    ' 01 Jan 2020

    Const Password As String = "MySecret"
    Dim Pw As String

    ' Here "Sheet2" is the CodeName of tab "Other Sheet"
    ' You can rename the tab without affecting the code's functionality.

    ' Tab "Hidden Sheet" is the sheet to require a password to access.
    ' This code refers to it by its CodeName 'MySheet'.

    ' You can change the CodeName in the Properties window.
    ' It's the first item in the list, marked "(Name)"
    '   - The Properties window will appear on the left of the
    '     VB Editor window if enabled from the VBE 'View' menu.
    '   - You may choose to match the CodeNames of the sheets in your
    '     project to the CodeNames used here or change the code below
    '     to match the CodeNames you allocate in your project.
    '     (Use Edit/Replace to change the names in the code)


    ' Ensure that the sheet to be hidden isn't the active one.
    ' Therefore activate another sheet:-
    If Not (ActiveSheet Is Sheet2) Then Sheet2.Activate

    ' Hide 'MeSheet'. It can't be made visible from the UI.
    MySheet.Visible = xlSheetVeryHidden

    Do
        Pw = InputBox("Enter the password to access" & vbCr & "the """ & _
                      MySheet.Name & """ tab.", "Password required")

        ' Enter nothing or press Cancel to exit without gaining access.
        If Len(Pw) = 0 Then Exit Do

        If StrComp(Password, Pw, vbBinaryCompare) Then
            MsgBox "Sorry, the password you entered isn't" & vbCr & _
                   "recognized. Please try again." & String(2, vbCr) & _
                   "Note that the password is case sensitive.", _
                   vbInformation, "Access denied"
        Else
            With MySheet
                .Visible = xlSheetVisible
                .Activate
            End With
            Exit Do
        End If
    Loop
End Sub

The code will run automatically whenever the workbook is opened again. It displays an InputBox asking for the password. If no password is given the user gains access to all worksheets except the hidden one. If the correct password is entered full access to that sheet is granted until the workbook is closed again.

The password is set in the code. Change it whenever you like. Look for the line

Const Password As String = "MySecret"

Anyone with access to the code - the VBA Project - can both, know the password, change the password or simply make your hidden sheet visible (look for the Visible property in the Properties window on the left). Therefore you should prevent such access.

1. In the VB Editor, right-click anywhere in the list of your workbook's components (You might have several workbooks open at that time, so click on the correct project). If the Project Explorer window isn't visible (it's visible by default at the top left), enable it from the View menu.
2. Your right-click will open a drop-down menu. Select VBAProject Properties...
3. In the dialog box that opens select the Protection tab.
4. Lock the project for viewing, set and confirm a password. Note that you will have severe trouble if you forget the password. Write it down before you set it. Lots of people get locked out of their own projects. 

Except for the the protection of the VBA Project, the above is all demonstrated in the attached workbook.