Password protect excel using VBA for a shared Excel file

Password protect Excel Sheet based on the User as part of excel vba tutorial. For Example, you have a master excel sheet in your centralized location. Now everyone can access the same file and if someone changes something on that it is very difficult or hard to track who has modified the same.

Password protect excel sheet shared by multiple users:

We can easily set permission for each user password protect excel using VBA. Only managers have the access to the manager sheet, Executive has the permission on the Executive sheet and the admin has the control on the excel sheet. My Dummy data looks like below. Dataset-Set-permissionFrom the above pictures, you can understand that there are 4 users can get access to this workbook and Rishabh will have the admin permission. Niladri and Mohit are the executives and they will have the access to “Executive” sheet. Lastly, Manoj is the manager and he will have the access in “Manager” Sheet.

Suggested: Beginner Guide to Excel VBA (Macros)

So to create user form, do the following steps:

  1. Create a User Form which will look like below.
    1. Press Alt + F11 to go the VBA page
    2. Click on Insert and then UserFormCreate-UserForm-for-Set-Permission-1024x529
  2. Draw 2 Labels and change the caption as UserName and Password
  3. Draw 1 text box and give name as txtpassword
  4. Draw 1 combo box and give name as txtusername
  5. Draw 2 command box and give name as cmdok and cmdcancel

Your userform should look like below: Userform-Set-PermissionOne thing you need to set here is the passwordChar properties as *.PasswordChar-Set-Permission

To set username wise permission you would need to do the following:

    1. Double click OK button and then paste the below macro code.

Private Sub cmdcancel_Click()

Application.DisplayAlerts = False

ThisWorkbook.Save

Unload Me

End Sub

Private Sub cmdok_Click()

On Error Resume Next

Dim username As String

Dim password As String

Dim permission As String

username = Me.txtusername.Value

permission = Application.WorksheetFunction.VLookup(username, Sheets("Data").Range("A2:C5"), 2, 0)

password = Application.WorksheetFunction.VLookup(username, Sheets("Data").Range("A2:C5"), 3, 0)

If password <> Me.txtpassword.Value Then

MsgBox "Invalid Passord"

Exit Sub

End If

If password = Me.txtpassword.Value And Me.txtusername.Value = "Rishabh" Then

Sheets("Manager").Visible = True

Sheets("Executive").Visible = True

Sheets("Manager").Select

Unload Me

End If

If password = Me.txtpassword.Value And Me.txtusername.Value <> "Rishabh" Then

Sheets(permission).Visible = True

Sheets(permission).Select

End If

Unload Me

End Sub


You need to fix one more setting also i.e. When someone first opens the file he/she will give the username and give the password and then will press the “OK” button.
  • To get the User form when you first open the file
  • Go to VBA page (Alt + F11)
  • Then Double click on this Workbook and paste the below macro code
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayAlerts = False

Sheets("Manager").Visible = xlVeryHidden

Sheets("Executive").Visible = xlVeryHidden

ThisWorkbook.Save

ThisWorkbook.Close

End Sub


Private Sub Workbook_Open()

UserForm1.Show

End Sub

As you can see I have created only four users right now, Niladri (Executive), Rishabh (Admin), Mohit (Executive) and Manoj (Manager) with password as [email protected]$ in this pattern. Download the Excel Macro workbook from here and play with it. We’d love to hear from you so Is there anything more you like us to cover, feel free to post in the comment section below.

If you have missed any of our Data Management using Excel VBA then you can click here to go through our article. You can also learn Excel macro tutorial session for free, just click here on the link

2 Responses
  1. Matt Duncan

    Hi! This is very similar to what I am trying to do. I have a variety of workbooks that need to be protected. The workbooks relate to multiple companies and each company will have its own PW. I have a table of 100 passwords for each of 16 companies and the password will change periodically (monthly or quarterly). I would like to have a user form that shows Company # and the password # 1-100. With this information the vba would protect the workbook by vlookup or index match to the appropriate password for the company #. I forsee the issue with vlookup is if we choose PW# 10 in the pw # box the vlookup would need to lookup result in column 11 of the table. Index match may work better or a formula =PW# + 1.

    any help is appreciated!!!!

  2. Mahek

    Since so long i was searching to crack this one. Thanks for making it possible. This blog has been very helpful

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.