Password-Protect a Worksheet from Viewing
Keep prying eyes from viewing a worksheet, unless they have the password.
With the aid of some Excel VBA code placed the Private module of the workbook object, you can protect a worksheet from viewing by anyone who doesn't knowthe password you select. The code stops after three failed attempts. A runtime error will occur, you will not be able to view the worksheet, and it does not mask the password entry. You'll need to close and reopen the workbook to try again.
This method is far from secure and should not be used if the worksheet contains highly sensitive information. It only enhances the general worksheet protection and uses the UserInterfaceOnly option of the Protect method. You should also protect/lock Excel VBA code.
The code in this tutorial makes use of the worksheet's CodeName. The Workbook_Open procedure ensures that the workbook does not open with the unviewable worksheet being active.
The Code
Right-click on the sheet tab, select ViewCode, double-click on ThisWorksheet, and insert the following code:
Dim sLast As Object Private Sub Workbook_Open( ) 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1.Name = ActiveSheet.Name Then Sheet2.Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim strPass As String Dim lCount As Long If Sh.CodeName <> "Sheet1" Then 'Set sLast variable to the last active sheet _ This is then used to return the user to the _ last sheet they were on if password is not known _ or they Cancel. Set sLast = Sh Else 'Hide Columns Sheet1.Columns.Hidden = True 'Allow 3 attempts at password For lCount = 1 To 3 strPass = InputBox(Prompt:="Password Please", Title: ="PASSWORD REQUIRED") If strPass = vbNullString Then 'Cancelled sLast.Select Exit Sub ElseIf strPass <> "Secret" Then 'InCorrect password MsgBox "Password incorrect", vbCritical, "Ozgrid.com" Else 'Correct Password Exit For End If Next lCount If lCount = 4 Then 'They use up their 3 attempts sLast.Select Exit Sub Else 'Allow viewing Sheet1.Columns.Hidden = False End If End If End Sub
The password used in this code is Secret.
Save and close your workbook and reopen it. To run the code, enable your macros and then try selecting Sheet1. A blank screen and a dialog will appear, asking you for the required password. Remember, you only get three shots at it!
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing