Hello, Excellers and welcome back to another #MacroMonday #Excel tip blog post in my 2020 series. Today I want to show you how to password protect an individual worksheet in your Excel workbook. This is really handy if you have a particular worksheet you do not want users to access without entering a password. There are a few stages to this tip, it involves getting into some Excel Visual Basic code, but is very straightforward.
So, we have a workable example, a workbook with two worksheets, Data Summary and Raw Data. We do not want users to be able to access the Raw datasheet without a password. The sample sheets are in the screenshot below.
Before we get into writing some Excel VB we need to be able to access the VB editor. In order to do this we need to enable the Developer Tab. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you want to watch my YouTube video on How To Enable The Developer Tab click the link below.
Now, There are FOUR stages to this solution.
The first is to remove visibility of the Raw Data worksheet once the workbook is opened. Second, we hide the Raw Data worksheet when it is either clicked away from/out of. The third stage is to add a pop-up message box to the user to enable them to see the Raw Sheet only when they enter the correct password. Finally, the fourth and last stage is to attach the macro code from stage three to a button on the Data Summary worksheet. This will allow the user to enter a correct password to gain access to the Raw Data tab.
Stage 1. Use the Workbook Open Event.
The first stage is to use the Workbook Open event. This does exactly what you would imagine it would. When the workbook opens, whatever instructions or code you input here run. So, The following code is triggered when the file is opened. It switches the status of the Raw Data worksheet to very hidden. This means the user cannot see the worksheet even when they right-click to unhide and hidden worksheets.
[stextbox id=’info’]
Private Sub Workbook_Open()
If Sheets(“Raw Data”).Visible = True Then
Sheets(“Raw Data”).Visible = xlVeryHidden
End Sub
[/stextbox]
To enter this code follow the steps below.
- Double Click on ThisWorkbook in the VB project window.
- Select the Dropdown box on the left side and select Workbook.
- Take the Dropdown box on the right-hand side and click Open.
Stage 2. Use the Worksheet Deactivate Event.
The following code is triggered when the worksheet whenever it is de-selected or moved. This switches the status of the Raw Data worksheet to very hidden. Therefore, the user cannot see the worksheet even when they right right-click to unhide and hidden worksheets. This is the same as the step above.
- Double Click on the Raw Data worksheet in the VB project window.
- Take the Dropdown box on the left side and Select Worksheet.
- Select the Dropdown box on the right hand side and click Deactivate
[stextbox id=’info’]
Private Sub Worksheet_Deactivate()
Sheets(“Raw Data”).Visible = xlVeryHidden
End Sub
[/stextbox]
- Double Click on the Raw Data worksheet in the VB project window.
- Select the Dropdown box on the left side and Select Worksheet.
- Take the Dropdown box on the right-hand side and click Deactivate
Stage 3. Write The Macro To Allow Access To The Raw Data Sheet.
The next stage we get to write a Macro. Yes!. We are going to write a macro that prompts the user to enter a password (which we set) to allow access to the Raw Data sheet. Ok, let’s get going and write the code.
- Insert a new module into the workbook. Insert Menu | Module
- Copy the Code below into your new module.
- Change the password from “YourPassword” to a password of your choice.
[stextbox id=’info’]
Sub AccessRawDataSheet()
‘declare the password as variable
Dim strPassword As String
‘set the password as the string entered by the user into the Input box
strPassword = InputBox(“Please enter the password to access this sheet”)
‘the user enters a correct password to make the Raw Data sheet visible and active
If strPassword = “YourPassword” Then
Sheets(“Raw Data”).Visible = True
Sheets(“Raw Data”).Select
Else
‘an incorrect password will warn the user
MsgBox “Sorry – that is not the correct password”
End If
‘code ends
End Sub
[/stextbox]
The Visual Basic Code Explained To Password Protect An Individual Worksheet.
So, I have inserted a New Module into my current Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro AccessRawDataSheet().
[stextbox id=’info’]
Sub AccessRawDataSheet()
End Sub
[/stextbox]
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. Now, all that is needed is the rest of the code that password protect an individual or specific Excel worksheet.
Declaring Variables.
First, I need to declare some variables in this macro. This simply creates a memory container for the values. I have declared that the password will be a String data type.
[stextbox id=’info’]
Dim strPassword As String
[/stextbox]
Set The Password As the String Variable.
Next, the password will be set as the string entered by the user into an Input Box.
[stextbox id=’info’]
strPassword = InputBox(“Please enter the password to access this sheet”)
[/stextbox]
Testing If The Password Is Correct.
So, the next stage of the code will test the password. Using the IF ELSE statement, we can test if the password entered by the user is correct. If it is correct then the worksheet status is set to Visibile again and made active. If the password is incorrect then a message to the user is displayed. You can change the message in the code below to tailor the instructions to the user.
[stextbox id=’info’]
If strPassword = “YourPassword” Then
Sheets(“Raw Data”).Visible = True
Sheets(“Raw Data”).Select
Else
MsgBox “Sorry – that is not the correct password”
End If
[/stextbox]
Ending The Macro.
The code ends when either the password was entered correctly and the Raw Data tab is displayed, or the message box alerts the user to an incorrect password. This is done with the “End Sub” piece of code. This was already entered into the module for us when started the type the name of the macro. We now have the code for password protecting and individual worksheet.
[stextbox id=’info’]
End Sub
[/stextbox]
Finally, Attach The Macro To A Button On The Data Summary Worksheet.
The next stage we need to attach the Macro to a button on the Data Summary Worksheet. When the user clicks the button they will be prompted for the password. If it is correct the Raw Data worksheet will become visible. If it, not the correct password then they get a warning it is not the correct password. This is how to code explained above is executed to password protect an individual or specific worksheet.
- Developer Tab | Controls Group | Insert | Form Controls | Button
- Draw button | Assign Macro | Select Macro
- In the Assign Macro Dialog Box Select your Macro
- If you want to re-name your button do so – I have renamed mine just right Click and Edit the text
So, the final stage of this is to test the code!. Job Done.
So, how handy is that?. You can now password protect an individual or specific worksheet.