You can password-protect a sheet from being edited very easily, by selecting Protect Sheet from the Review menu on the ribbon.

 

 

However this standard protection doesn’t stop the user from being able to view the sheet, it just prevents them from being able to edit the sheet, choosing whether to enable filtering, formatting, etc.

 

Imagine you have two worksheets, MainMenu which should always be visible and SalesStats which should only be accessed by those with the password because it contains sensitive data.

 

Using normal in-built Excel sheet protection, once unprotected that’s it and users permanently have access to the sheet without the password. But with the use of some VB code, you can ensure whenever the sheet is clicked away from or the file is closed/opened, the sheet re-protects itself and cannot be accessed without the password.

 

In the SalesStats sheet, for the deactivate event use the following code to ensure the sheet becomes Very Hidden whenever it is de-selected.

 

 

For the Workbook Open event, add the following code to ensure the sheet is always hidden on opening the file.

 

 

On the MainMenu sheet, include a button to run the below macro to access the SalesStats sheet:

 

 

Sub AccessSalesStats()

    Dim strPassword As String
   
    strPassword = InputBox(“Please enter the password to access this sheet”)
   
    If strPassword = “YourPassword” Then
        Sheets(“SalesStats”).Visible = True
        Sheets(“SalesStats”).Select
    Else
        MsgBox “Sorry – that is not the correct password”
    End If
   
End Sub

 

On triggering the macro, the user will be confronted with this input box before they are able to access the sales statistics.