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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- 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!
