Excel Password Protection - Part 4 | Pro-business Excel VBA Programming

There are situations that you need to hide a customized menu or to make worksheets read only from certain users that need your Excel file for informational purposes only and they shouldn't mess around with the important or confidential data in the worksheets.

This is the final post of the 4-part article about Excel Password Protection. We'll discuss here how to create a VBA program to make your worksheets and customized menu safe from unauthorized users. Just follow the steps below:

Step 1: From the previous post, open the file that we created. It should have the menu program that we entered in it. Open the VBE (you should know it by now or read the previous post, click here to learn about it).

Step 2: Create a new user form by clicking Insert>UserForm. The user looks like the screen shot below. It's the UserForm1. Beside it you'll see the Toolbox menu. It is used to put controls inside the form like a button, label and a text field.

Step 3: Open up the properties window by clicking View>Properties Window or by pushing F4 key. The properties window looks like the figure below:

Step 4: Click the new user form so that the properties window will show the form's properties. Inside that properties window, change the following form properties:

Name: change it to "frmPassword"
Caption: change it to "Enter Password"
Height: 111
Width: 210

Step 5: From the tool box window, drag and drop one label, textbox and a button to the user form. Once controls are put in the form, change the properties of each according to the values below:

Label
Name: lblPassword
Caption: Password
Width: 42
Height: 12
Top: 28
Left: 24
Textbox
Name: txtPassword
Width: 120
Height: 18
Top: 24
Left: 72
Button
Name: cmdOk
Caption: Ok
Width: 48
Height: 18
Top: 60
Left: 144

Step 6: Create a new module by clicking Insert>Module in the VBE menu. At the new module enter the following code: (copy-paste will do it quick)


'indicator that password is ok or not
'true = ok, false is not
Public PasswordOk As Boolean

'temporary storage of the
'password entered by the user
Public strPassword As String

Sub UnlockSystem(strPW As String)
 PasswordOk = False 'initialize to false

  'if password entered is incorrect
  'it will generate an error and
  'will not proceed in menu activation
 On Error GoTo MyErr
    Sheet1.Unprotect Password:=strPW
    PasswordOk = True
    strPassword = strPW
    PutProBusinessMenu
 On Error GoTo 0
 Exit Sub
MyErr:
 MsgBox "Worksheet 1 will remain locked and" & _
 " customized menu is hidden.", , "Invalid Password"
End Sub

Sub LockSystem()
 'this will be used to lock the program
 'and disable the customized menu
 'when changing workbook and closing the file
 If PasswordOk Then
    Sheet1.Protect Password:=strPassword
    ClearProBusinessMenu
 End If
End Sub

Step 7: Go back to the user form and double click the button control in it. The code view for the button is displayed. Enter the following code inside it:


'when the ok button is clicked,
'it runs the UnlockSystem routine
Private Sub cmdOk_Click()
 UnlockSystem txtPassword.Text
 Unload Me
End Sub

Step 8: Finally change the code of the entire ThisWorkbook object and put the following code:


Private Sub Workbook_Activate()
  'if a workbook is activated,
  'it will unlock the system if password
 'previously entered was correct
 If PasswordOk Then
    UnlockSystem strPassword
 End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ;lock again the system before closing
 LockSystem
End Sub

Private Sub Workbook_Deactivate()
  'locking the system when workbook is deactivated.
 LockSystem
End Sub

Private Sub Workbook_Open()
  'when the file is open, it prompts for the
  'password
 PasswordOk = False
 frmPassword.Show
End Sub

Step 9: Lastly, lock the first sheet of your excel file (the one that has the object name of Sheet1) using any password that you want. The same password will be used to unlock it and enable the menu.

There you go. try to run the program by closing the file and reopen it. The file will ask for the password. If the correct password is entered, you will see the customized menu and the worksheet is unlocked. Otherwise, menu and worksheet is locked.