Password Protect Worksheet From Viewing

Excel VBA: Password Protect Worksheet From Viewing. Prevent Worksheet Viewing

Got any Excel/VBA Questions? Excel Help

Password Protect Worksheet From Viewing

With the aid of some Excel VBA code placed the private module of the Workbook Object (ThisWorkbook) we can password protect a Worksheet from viewing to all those that do not know the password.

IMPORTANT: It should be noted that this method is far from secure and should not be used if the Worksheet contains highly sensitive information. It is also addition to the general Worksheet protection and uses the user interface only option of the Protect Method. You should also protect/lock excel VBA code. 

The code shown below makes use of the worksheets code name.

It does not mask the password entry, but does stop after 3 failed attempts. If you wish to mask the password, use a Textbox on a UserForm that has its PasswordChar Property set to *. 

The workbook open procedure is there to ensure the Workbook does not open with the un-viewable Worksheet being active.

To use this code: While in Excel proper, right click on the Excel icon, top left next to File and choose View Code it is in here you must paste the code below and change to suit your needs.

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

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery