Require a Password to View Hidden Worksheets in Excel - VBA Tutorial

Add to Favorites
Author: | Edits: don

Full Course

A simple macro that allows you to require a password in order to view hidden worksheets.

This adds a nice user-interface and data management solution to your workbook and I show you how to apply it to any workbook.

This works by showing the user a password input window and, if they input the correct password, they can view the hidden worksheets, otherwise the worksheets stay hidden and cannot be viewed.

The hidden worksheets in this tutorial cannot be viewed by right-clicking a sheet tab and clicking Unhide and they cannot be viewed by manipulating the project itself, because that will also be protected.

Code to Show/Hide Worksheets Using a Password

View the video above to get a detailed explanation of how this code works.

' Excel VBA Tutorial - https://www.TeachExcel.com
'
' Hide Worksheets Using a Password
'
' Specific Sheets will not be visible until the user enters the correct password.
'
' If you don't password protect the project, the user can still unhide the
' worksheet.
'
' Password Protect the Project: Right-click the Project in the Project Explorer >
' VBAProject Properties > Protection tab > check Lock project for viewing and
' input a password and hit OK.
 
Option Explicit ' Require that variables are declared.
 
' The Password
' A global constant value allows you to store this password once for your entire
' project and use it wherever you need to check for the password. This ensures
' that you only have to change the password in one place in the future.
Public Const conSheetPassword As String = "123456"
 
Sub ViewSheet()
' Code from: https://www.TeachExcel.com
'
' Unlock/show the worksheet if the user inputs the correct password.
 
    ' Variables.
    Dim userInput As Variant
    
    ' Ask the user for the password to unlock the sheet.
    userInput = InputBox( _
        Prompt:="Input a password to unlock the worksheets.", _
        Title:="Password Input", _
        Default:="Password")
 
    ' Check if the correct password was input.
    ' - Trim() removes any space that might have been accidentally added to the
    '   start or end of the password.
    ' - LCase() ensures that a case-insensitive check is being performed because
    '   it converts the input and the password both to lower case.
    If LCase(Trim(userInput)) = LCase(conSheetPassword) Then
        ' Pass - all is good.
        
        ' Show the Sheet.
        Sheets("Helper").Visible = xlSheetVisible
        Sheets("Raw").Visible = xlSheetVisible
        
        ' Go to the sheet.
        Sheets("Helper").Select
    
    Else
        ' Fail - wrong password.
        
        ' Let the user know.
        MsgBox "Incorrect password. Access Denied."
    
    End If
 
End Sub
Sub HideSheet()
' Code from: https://www.TeachExcel.com
'
' Hide the worksheet so that the user cannot unhide it by hand.
'
' If you don't password protect the project, the user can still unhide the
' worksheet.
'
' Password Protect the Project: Right-click the Project in the Project Explorer >
' VBAProject Properties > Protection tab > check Lock project for viewing and
' input a password and hit OK.
    
    ' Hide the sheet 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
 
End Sub
 

Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Our Excel Courses

Professional Input Forms in Excel

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

Send Emails from Excel

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.