How to set passwords to protect individual worksheets for different users in Excel?
In Excel, you can set different passwords for different worksheets, 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.
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in