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!