Subscribe!

I am often asked how to go beyond the simple worksheet and workbook password protection in Excel. In this article, I will outline how to setup a workbook where each user has a different password.

  1. Open a new workbook and name one sheet login, one sheet pass and one sheet data.
  2. The “pass” worksheet is where the usernames and passwords will be installed. We will put the username in column a and the password in column b. Enter “testname” in a1 and “testpass” in b2.
  3. Next we need to define the range of where the names and passwords are. Choose insert from the menu then name=>define. In the names in workbook field, enter “Users”. In the “refers to” box at the bottom, enter =pass!$A$1:$B$100 then select OK.
  4. On the login sheet enter “User:” in A2 and ”Password:” in A3. The user will enter their username in B2 and password in B3. Next we need a command button. On the view=>toolbars menu, make sure “control toolbox” is selected. On the control toolbox menu select the command button and then click cell c2, place the command button where you would like.  You can change the text on the command button to something like “login” or “Go” (right click and choose command button object=>edit). Note: “Design Mode” on the control toolbox menu must be selected to edit the command button and must be deselected to run the button.
  5. Next we need to enter some code for the button, double click the command button while in design mode. The VB Editor will open.  In between the Private Sub CommandButton1_Click() line and End Sub, paste the code in step 6:
  6. Dim strUserName As String
       strUserName = ActiveSheet.Range(“B2″)
       If Application.VLookup(strUserName, Sheets(“pass”).Range(“Users”), 2, 0) = ActiveSheet.Range(“B3″) Then
        ActiveWorkbook.Unprotect Password:=”"
           Sheets(“data”).Visible = True
       Else
           MsgBox “Name and or Password rejected”
       End If
  7. On the left in the VBA project window (you may need to select “project explorer” from the menu to see this) double click on “ThisWorkbook” and paste the code in step 8:
  8. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Unprotect Password:=”"
    Worksheets(“data”).Visible = False
        Sheets(“login”).Select
        Range(“b2:b3″).Select
        Selection.ClearContents
    ActiveWorkbook.Save
    ActiveWorkbook.Protect Password:=”"
    End Sub
  9. Close the VB Editor.
  10. Hide the worksheet “data” (format=>sheet=>hide) and hide the worksheet “pass” then password protect the workbook tools=>protection=>workbook).  Leave the password field blank when protecting the workbook.
  11. Save the workbook.

You should now have a working password driven workbook for multiple users. To test it out, enter “testname” in cell B2 of the login page. Enter “testpass” in cell B3. Click the button (must have exited design mode). The data sheet should unhide. That is it!

Now you just need to make it your own by entering all your users names and passwords. You may also want to enter an actual password for the protect workbook rather than leaving it blank. If you do this, you need to change three places in the two sets of code above. Anywhere it says password= “”, change it to password=”your password”.

One last note. This is a very simple security setup. There are many things that can be done to make this even more secure such as compiling the VBA code and password protecting as well as securing the pass sheet better. With the current setup anyone who has a password could unhide the pass sheet and see the other users passwords. This could be circumvented by hiding the rows on the worksheet and protecting the individual sheet. So one final warning, anyone with basic VBA knowledge would be able to gain access if you only use the methods I outline here. This was just an introduction to Excel security possibilities.

Joseph Harris
Excel Security
Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. Conditional Formatting with Multiple Criteria in Excel Microsoft Excel has a limit of 3 criteria in Conditional...
  2. Excel Data Validation Data validation gives the Excel developer a way of dictating...
  3. #VALUE! Error in Microsoft Excel The #VALUE error is an error that is seen almost...
  4. Data Validation List on Seperate Sheet How to create list for data validation on different sheets...
  5. Removing Macros from a Workbook Q: How do I remove all the macros from my...

This entry was posted on Friday, December 12th, 2008 at 12:43 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.