如何設置密碼以保護 Excel 中用戶的個人工作表？

在 Excel 中，您可以為不同的工作表設置不同的密碼，這意味著一個用戶可以使用一個密碼對一個工作表進行更改，而另一個用戶可以使用不同的密碼對另一個工作表進行更改。但是，有時，您只希望每個用戶能夠查看和訪問他們自己的工作表。這可以在Excel中解決嗎？

要單獨保護每個工作表並限制用戶對工作表的訪問，請應用以下 VBA 代碼：

1. 打開一個新工作簿，並創建一個名為“Main”的新工作表，見截圖：

2。然後按 Alt + F11鍵 鍵打開 Microsoft Visual Basic for Applications 窗口，雙擊 的ThisWorkbook 從左邊 項目-VBA項目 窗格以打開一個空白代碼模塊，然後將以下 VBA 代碼複製並粘貼到代碼窗口中，請參見屏幕截圖：

VBA 代碼：設置密碼以保護用戶的個人工作表

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