How to Set Different Privileges for Different Users of an Excel File

The Excel security is rather important for your work. In this article, we will introduce the method to set different privileges for different users by Excel VBA.

In our previous article How to Create a Login Window to Protect Your Excel Workbook, we have introduced the method to create a login window for your Excel workbook. But when several users need to get access to the document, you need to make additional settings. Besides, you need to guarantee that those users will not make wrong changes to the file. Now follow the steps below and see how to finish this task.

Set Different Privileges

Suppose now there are three sales representatives need to modify this file. The name and the password are set as the image show below:

And each person will need to modify a certain worksheet.

1. Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
2. And then insert a new user form into the editor.
3. In this step, insert two labels, two text boxes and two buttons into the user form.
4. And then change the caption for the user form and those elements that you insert in step 3. You can also refer to the image below:
5. After that, change the “PasswordChar” into “*” for the second text box.
6. After that, double click the user form.
7. Next input the following codes into the module:

Private Sub CommandButton1_Click()
  Dim objTargetWorksheet As Worksheet

  If (TextBox1.Value = "John" And TextBox2.Value = "234") _
    Or (TextBox1.Value = "Amy" And TextBox2.Value = "345") _
    Or (TextBox1.Value = "Paul" And TextBox2.Value = "456") Then
    Me.Hide: Application.Visible = True

    For Each objTargetWorksheet In ActiveWorkbook.Worksheets
      If objTargetWorksheet.Name = TextBox1.Value Then
        objTargetWorksheet.Unprotect Password:=12345
      Else
        objTargetWorksheet.Protect Password:=12345, DrawingObjects:=True, Contents:=True, Scenarios:=True
      End If
    Next
  Else
    MsgBox "Please input the right user name and the right password"
  End If
End Sub

Private Sub CommandButton2_Click()
  ThisWorkbook.Application.Quit
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  ThisWorkbook.Application.Quit
End Sub

In the codes, we will protect other worksheets. And users can only modify the corresponding worksheets. You can also change the passwords according to your need.

8. After that, double click the “ThisWorkbook” in the VBA project.
9. And then input the following codes into it:

Private Sub Workbook_Open()
  Application.Visible = False: UserForm1.Show
End Sub

10. In order to prevent the users to modify the VBA codes, you need to set password for the VBA projects. In our previous article How to Password Protect VBA Codes in Your Excel Worksheet, we have introduced methods. You can also set a special password.
11. In the end, save this Excel file.
12. The next time you open the file, the user form will not appear. You need to click the button “Enable Content” in the file.
13. And then you will see the “Log In” window. Input a name and the password into the two text boxes.
14. After that, click the button “Log In” in the window.

At this time, John can only modify his worksheet. He is unable to change other worksheets or the VBA codes. In addition, except for the three sales representatives, no one else can open this file without the password. The next time when you have such requirement, you can also set such a log in window for the workbook.

Recognize the Problem in Excel Corruption

At times Excel will corrupt as a consequence of numerous factors. Before you start fixing your files, you need to identify the problem along with the reasons for the data disaster. In addition, if you know nothing about data recovery, you can repair corrupt Excel xlsx by using a third-party tool. With the cutting-edge algorithm, this tool can handle almost all the Excel errors.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupted docx and outlook repair software products. For more information visit www.datanumen.com