如何设置密码以保护 Excel 中用户的个人工作表？

在 Excel 中，您可以为不同的工作表设置不同的密码，这意味着一个用户可以使用一个密码对一个工作表进行更改，而另一个用户可以使用不同的密码对另一个工作表进行更改。但是，有时，您只希望每个用户能够查看和访问他们自己的工作表。这可以在Excel中解决吗？

要单独保护每个工作表并限制用户对工作表的访问，请应用以下 VBA 代码：

1. 打开一个新工作簿，并创建一个名为“Main”的新工作表，见截图：

2。然后按 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口，双击 的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