+1-2033499909
[email protected]

Single Blog Title

This is a single blog caption
7 Apr 2017

VBA Code to password protect excel for a shared Excel file

/
Posted By
/
Comments0
/

In this blog, we will try to cover how to 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.

Step by step guide on how to password protect excel sheet shared by multiple users:

We can easily set permission for each user by password protect excel using VBA. Only managers have the access on 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.

how to password protect Excel Sheet based on the UserFrom 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 userform, 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 UserFormGo to VBA page and Create Userform for password protect excel
  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

Related article: Excel Password and Security

Your userform should look like below:

Userform for the password protect excel in the UserForm there two parameters User Name and PasswordOne thing you need to set here is the passwordChar properties as *.

You need to set the passwordChar properties as * - password protect excel

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