How to set passwords to protect individual worksheets by users in Excel?
In Excel, you can set different passwords for different sheets, this means that one user could make changes to one worksheet using one password, and another could use a different password to make changes to another worksheet. But, sometimes, you just only want each user to be able to view and access their own sheet. Is this possible to be solved in Excel?
To protect each worksheet individually and limit access to the worksheets by users, please apply the below VBA code:
1. Open a new workbook, and create a new sheet named as “Main”, see screenshot:
2. Then, press Alt + F11 keys to open the Microsoft Visual Basic for Applications window, double click ThisWorkbook from the left Project-VBAProject pane to open a blank code module, and then copy and paste the following VBA code into the code window, see screenshot:
VBA code: Set passwords to protect individual sheets by users
Option Explicit Dim gUserName As String Dim gUserPass As String Private Sub Workbook_Open() 'Updateby ExtendOffice Dim xWShs As Sheets Dim xWSh As Worksheet Dim xUserName As String Dim xPass As String Dim xBolH As Boolean GTINPUT: xUserName = InputBox("Enter the user name") If TypeName(xUserName) = "String" Then If xUserName = "" Then Exit Sub End If End If xUserName = LCase(xUserName) xPass = InputBox("User name:" & xUserName & Chr(13) & Chr(10) & "Enter the password:") If TypeName(xPass) = "String" Then If xPass = "" Then MsgBox "The password is incorrect, please enter the user name and password again." GoTo GTINPUT End If Else MsgBox "The password is incorrect, please enter the user name and password again." GoTo GTINPUT End If Set xWShs = Worksheets xBolH = False For Each xWSh In Worksheets If xWSh.Name = xUserName Then xBolH = True Exit For End If Next If xBolH Then Set xWSh = xWShs(xUserName) On Error GoTo GTINPUT2 xWSh.Unprotect (xPass) xWSh.Visible = True xWSh.Activate Else Set xWSh = xWShs.Add xWSh.Name = xUserName xWSh.Activate End If gUserName = xUserName gUserPass = xPass Exit Sub GTINPUT2: MsgBox "The password is incorrect, please enter the user name and password again." GoTo GTINPUT End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim xWSh As Worksheet On Error Resume Next Set xWSh = Worksheets(gUserName) xWSh.Protect Password:=gUserPass, DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True For Each xWSh In Worksheets If xWSh.Name <> "Main" Then xWSh.Visible = xlSheetVeryHidden End If Next xWSh ActiveWorkbook.Save End Sub
3. Then, put the cursor at the end of the Private Sub Workbook_Open() script, and press F5 key to run this code. Now, in the popped out prompt box, enter the user name that you want to create a sheet for this user, see screenshot:
4. And then, click OK button, in the following prompt box, enter a password for protecting this sheet, see screenshot:
5. Go on clicking OK button, and a new sheet named with the user name is created at once, and you can create the data for that user as you need. See screenshot:
6. Repeat the above step 3 – step 5 for creating other sheets one by one that you want to use.
7. After creating the sheets, save the current workbook by clicking File > Save As., In the Save As dialog box, specify a file name and then choose Excel Macro-Enabled Workbook (*.xlsm) format from the Save as type drop down list, see screenshot:
8. Then click Save button to save this file.
9. And then close the workbook and reopen it, then, click Enable Content at the top of the formula bar to activate the code, see screenshot:
10. Now, a prompt box will pop out to remind you enter the user name and password to open the specific sheet by certain user.
11. At last, when sending this workbook to other users, you should send the user name and password to the user. They will only open and edit their own sheets, and don’t have an authoriztion to see other worksheets.