vba - 如何使用动态密码保护 Workbook_Open 事件上的工作表

Question

我有这个代码:

Private Sub Workbook_Open()

Dim wSheet As Worksheet
Dim my_code As String
Dim x As Long, y As Integer
Dim Pass_word, Prev_pass As String

my_code = Val(Format(Date, "#")) * 397

Pass_word = create_pass(my_code)

For Each wSheet In Worksheets
    On Error GoTo errhandler
    wSheet.protect Password:=Pass_word, _
    UserInterFaceOnly:=True
Next wSheet

Exit Sub
errhandler:

x = 1
Do
my_code = Val(Format(Date - x, "#")) * 397
Prev_pass = create_pass(my_code)
For Each wSheet In Worksheets
    On Error GoTo move
    wSheet.Unprotect (Prev_pass)
Next wSheet
move:
If Err.Number <> 0 Then
    x = x + 1
    y = 0
    Err.Clear
Else
    For Each wSheet In Worksheets
        wSheet.protect Password:=Pass_word, _
        UserInterFaceOnly:=True
    Next wSheet
    y = 1
End If
Loop Until y = 1

Resume Next

End Sub

我想要做的是使用每天根据日期更改的动态密码来保护工作簿内的工作表。
我遇到的问题是如何每天更改设置的密码。
我添加了 errhandler 例程，但它不起作用。
create_pass 是我创建的用于生成编码通行证的函数，我已经对其进行了测试并且它返回了正确的值。

这里某处发生错误:

For Each wSheet In Worksheets
    On Error GoTo move
    wSheet.Unprotect (Prev_pass)
Next wSheet

没有突出显示的行，因此我无法确定到底是哪一行。
我在 Prev_pass 和 x 上添加了监视，它获取第一个值，然后抛出错误。
返回的错误是:

Run-time error '1004":
Application-defined or object-defined error

非常感谢任何帮助。

Answer 1

就像我在评论中提到的，我们可以使用临时表来存储密码。原因很简单。让我们看一下这个场景。假设工作簿在 16/12 上使用 16122013 作为密码进行保护。现在该密码也将存储在隐藏的临时表中。我们需要这样做，因为如果您在 17 日 或任何其他日期(可能是 1 年后？)打开文件，那么它会读取旧密码并取消保护工作表，然后再次保护它使用新密码。为了保护工作表，如果工作表受到保护，则必须先取消保护它。要知道旧密码，您必须从某个地方检索它或破解它。

这是另一种方法。

这将循环 365 次，并尝试根据您的函数 create_pass 取消对工作表的保护。我已经对代码进行了注释，因此您在理解它时不会遇到任何问题。但如果您这样做了，那么只需回发即可。

Sub Sample()
    Dim my_code As String, Ret As String
    Dim PrevDt As Date
    Dim n As Long

    my_code = Val(Format(Date, "#")) * 397

    '~~> Create the password
    Ret = create_pass(my_code)

    '~~> Loop till the password is not found
    Do
        '~~> Attempt to unprotect the sheet
        On Error Resume Next
        ActiveSheet.Unprotect Ret
        On Error GoTo 0

        '~~> Check if the sheet was unprotected
        If ActiveSheet.ProtectContents = False Then Exit Do

        '~~> If not then go back one date
        If PrevDt = #12:00:00 AM# Then PrevDt = Date - 1 Else PrevDt = PrevDt - 1

        my_code = Val(Format(PrevDt, "#")) * 397

        Ret = create_pass(my_code)

        '~~> This counter is required so that we can exit the loop after 365 days
        n = n + 1

        If n > 365 Then
            MsgBox "More than 365 passwords have been checked. Exiting now"
            Exit Do
        End If
    Loop
End Sub

编辑 1:(发布实际的 Workbook_Open 事件)

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim my_code As String, Ret As String, my_pass As String
Dim PrevDt As Date
Dim n As Long

my_code = Val(Format(Date, "#")) * 397

'~~> Create the password
Ret = create_pass(my_code)
my_pass = Ret

'~~> loop in all WS
For Each ws In Worksheets
    '~~> Loop till the password is not found
    Do
        '~~> Attempt to unprotect the sheet
        On Error Resume Next
        ws.Unprotect Ret
        On Error GoTo 0

        '~~> Check if the sheet was unprotected
        If ws.ProtectContents = False Then Exit Do

        '~~> If not then go back one date
        If PrevDt = #12:00:00 AM# Then PrevDt = Date - 1 Else PrevDt = PrevDt - 1

        my_code = Val(Format(PrevDt, "#")) * 397

        Ret = create_pass(my_code)

        '~~> This counter is required so that we can exit
        '~~> the loop after 365 days
        n = n + 1

        If n > 365 Then
            MsgBox "More than 365 passwords have been checked. Exiting now"
            Exit Do
        End If
    Loop
    '~~> protect with the current day password
    ws.protect my_pass, , , , True
Next ws

End Sub

这会在打开时取消对 WS 的保护，然后使用当前密码对其进行保护。