excel - 访问密码阻止表 - Excel

Question

下面的代码通过隐藏工作表来限制访问，除非输入密码。如果输入正确，则可以从各个选项卡中查看工作表。但是，它不会让我查看然后编辑工作表。

可以对此进行调整以允许用户输入密码，然后查看和编辑工作表吗？

Private Sub Workbook_Open()
Sheets("Sheet1").Visible = xlSheetHidden
End Sub

Public ViewAccess As Boolean       'In restricted sheet's activate event
Private Sub Worksheet_Activate()
If ViewAccess = False Then
Me.Visible = xlSheetHidden
Response = Application.InputBox("Password", xTitleId, "", Type:=2)
    If Response = "123" Then
        Me.Visible = xlSheetVisible
        Application.EnableEvents = True
        ViewAccess = True
    End If
End If
End Sub

标签： excelvbaviewpasswords

Answer 1

以下代码将为您提供帮助。当用户选择带有名称的工作表时，HiddenSheet它会要求输入密码。如果密码正确，则允许编辑数据，否则将自动转到上一个工作表，您必须更改HiddenSheet工作表名称。

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheetName As String

MySheetName = "HiddenSheet" 'The sheed which I want to hide.

If Application.ActiveSheet.Name = MySheetName Then
    Application.EnableEvents = False
    Application.ActiveSheet.Visible = False
    response = Application.InputBox("Password", "Enter Password", "", Type:=2)

        If response = "123456" Then 'Unhide Password.
            Application.Sheets(MySheetName).Visible = True
            Application.Sheets(MySheetName).Select
        End If
End If

Application.Sheets(MySheetName).Visible = True

Application.EnableEvents = True
End Sub

代码截断：