Excel - 将工作表的 View 限制为某些用户

Question

我有一个包含多个工作表的 excel 工作簿。

我想要做的是有一种机制，比如用户表单或用户可以向几个可能的用户之一进行身份验证的东西。

根据提供的用户名，我想显示某些工作表并隐藏其他工作表，并阻止用户访问他们不应查看的工作表。

有没有人在 Excel 中做过类似的事情？

任何想法表示赞赏
肖恩

Answer 1

我真的很喜欢打字这个任务。请记住，VBE 在此代码中不 protected ，因此您可能需要添加一些保护，但这应该可以满足您的需要。

您还应该创建一个通用的 Login工作表。这将是在输入密码之前打开的唯一工作表。这是必不可少的，因为您无法在不引发错误的情况下隐藏每张工作表。 (您需要有 1 张可见的工作表)。

WARNING: This code is mildly tested. You are responsible for any loss of data for using the below code, such as (but not limited to) forgetting a password. You have been warned!!!!

1. 打开工作簿，然后调用 GetLogin

Option Explicit

Private Sub Workbook_Open()

    GetLogin 1

End Sub

2.登录码

Private Sub GetLogin(ByVal AttemptNumber As Integer)

    Dim Sheet As Worksheet

    With ThisWorkbook.Worksheets("Login")
        .Visible = xlSheetVisible
        .Activate
    End With

    For Each Sheet In ThisWorkbook.Sheets
        If Not Sheet.Name = "Login" Then
            Sheet.Visible = xlSheetVeryHidden
        End If
    Next Sheet

    Dim Password As String
    Password = Application.InputBox("Please enter your password")

    Select Case Password
        Case "Ma$terPas$"
            For Each Sheet In ThisWorkbook.Sheets
                Sheet.Visible = xlSheetVisible
            Next Sheet
            ThisWorkbook.Worksheets(1).Activate 'For when you hide login sheet
        Case "Oth3Rpa$$"
            With ThisWorkbook
                .Worksheets(1).Visible = xlSheetVisible
            End With
            ThisWorkbook.Worksheets(1).Activate 'For when you hide login sheet
        Case Else
            If AttemptNumber <= 3 Then
                If MsgBox("You entered an incorrect password", vbRetryCancel, "Attempt # " & AttemptNumber) = vbRetry Then
                    AttemptNumber = AttemptNumber + 1
                    GetLogin AttemptNumber
                Else
                    ThisWorkbook.Saved = True
                    ThisWorkbook.Close
                End If
            Else
                ThisWorkbook.Saved = True
                ThisWorkbook.Close
            End If
    End Select

    ThisWorkbook.Worksheets("Login").Visible = xlSheetHidden

End Sub

3. 关闭工作簿

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ThisWorkbook.Saved = False Then
        If MsgBox("Would you like to save?", vbYesNo) = vbYes Then
            ThisWorkbook.Save
        End If
    End If

    Dim Sheet As Worksheet
    With ThisWorkbook.Worksheets("Login")
        .Visible = xlSheetVisible
        .Activate
    End With

    For Each Sheet In ThisWorkbook.Sheets
        If Not Sheet.Name = "Login" Then
            Sheet.Visible = xlSheetVeryHidden
        End If
    Next Sheet

    'Prevent from being asked to save the fact you just hid the sheets
    ThisWorkbook.Saved = True

End Sub

Ensure that the Workbook_Open and Workbook_Close are in your Workbook's Module.