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.
- Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
- And then insert a new user form into the editor.
- In this step, insert two labels, two text boxes and two buttons into the user form.
- 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:
- After that, change the “PasswordChar” into “*” for the second text box.
- After that, double click the user form.
- 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.
- After that, double click the “ThisWorkbook” in the VBA project.
- And then input the following codes into it:
Private Sub Workbook_Open() Application.Visible = False: UserForm1.Show End Sub
- 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.
- In the end, save this Excel file.
- The next time you open the file, the user form will not appear. You need to click the button “Enable Content” in the file.
- And then you will see the “Log In” window. Input a name and the password into the two text boxes.
- 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.
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