时间戳

首页 > 解决方案 > 取消保护工作表时跳转到错误处理程序

excel - 取消保护工作表时跳转到错误处理程序

问题描述

我使用 Web 上的 VBA 代码保护和取消保护我的工作表。

保护：

    Dim pwd1 As String, pwd2 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    pwd2 = InputBox("Please re-enter the password")

    If pwd2 = "" Then Exit Sub

    'Check if both the passwords are identical
    If InStr(1, pwd2, pwd1, 0) = 0 Or _
    InStr(1, pwd1, pwd2, 0) = 0 Then
        MsgBox "Password not matching. Please retry."
        Exit Sub
    End If

    For Each ws In Worksheets
        ws.Protect Password:=pwd1
    Next

    MsgBox "All worksheets Protected."

Exit Sub

解除保护：

    On Error GoTo ErrorOccured

    Dim pwd1 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    For Each ws In Worksheets
        ws.Unprotect Password:=pwd1
    Next
    MsgBox "All sheets UnProtected."

    Exit Sub

    ErrorOccured:

    MsgBox "Sheets could not be UnProtected - Password Incorrect"
Exit Sub

保护工作正常。使用正确的密码输入解除保护成功，但宏仍跳转到ErrorOccured错误处理程序并显示消息

表格无法解除保护 - 密码不正确

仍然出现。

出了什么问题？我该如何解决？

标签： excelvba

解决方案

我使用这些多年：

Sub Schutz_ALLE_mit_PW_Abfrage()

nochmal:
pw1 = InputBox("Passwort eingeben")
pw2 = InputBox("Passwort nochmal eingeben")

If pw1 = pw2 Then GoTo los
If MsgBox("Passwortwiederholung war falsch", vbRetryCancel) = vbRetry Then GoTo nochmal
Exit Sub

los:
For Each blatt _
In ActiveWorkbook.Sheets
On Error Resume Next
On Error GoTo weiter

If blatt.ProtectContents Then GoTo weiter
    blatt.Protect userinterfaceonly:=True, Password:="mb_neu", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
        blatt.EnableSelection = xlNoRestrictions
        blatt.EnableAutoFilter = True
        blatt.EnableOutlining = True
weiter:
Next
End Sub

Sub Schutz_ALLE_weg()
pw1 = InputBox("Passwort eingeben")
For Each blatt _
In ActiveWorkbook.Sheets
blatt.Activate
ActiveSheet.Unprotect Password:=pw1
Next
End Sub

推荐阅读