You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Tuesday, May 13, 2008

Excel Password Protection - Part 4

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.



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


0 comments: