excel - 在一个工作表中运行多个 'Private Sub Worksheet_Change(ByVal Target As Range)'

Question

我试图将 2 个代码合并到一个 Private Sub 中，而第一个代码运行良好，第二个代码根本没有被接收。它不返回任何错误，只是不调用所需的 Sub。任何帮助将不胜感激。

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo justenditall
    Application.EnableEvents = False


    If Not Intersect(Target, Range("e6:e1000, M6:m1000")) Is Nothing Then
        If Target.Value <> "" Then
        ActiveSheet.Unprotect Password:="password"
        Target.Locked = True
        ActiveSheet.Protect Password:="password"
        End If

    Next

    ElseIf Not Intersect(Target, Range("P1")) Is Nothing Then
        If Target.Value = 1 Then
        Call SetRecipients
        End If
    Next

justenditall:
    Application.EnableEvents = True
End Sub

Answer 1

您的代码有 Next这不是必需的。而你错过了End If .我很惊讶代码正在运行以执行第一个 IF/ENDIF

这对我有用 ( 久经考验)

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo justenditall
    Application.EnableEvents = False

    If Not Intersect(Target, Range("e6:e1000, M6:m1000")) Is Nothing Then
        If Target.Value <> "" Then
            ActiveSheet.Unprotect Password:="password"
            Target.Locked = True
            ActiveSheet.Protect Password:="password"
        End If
    ElseIf Not Intersect(Target, Range("P1")) Is Nothing Then
        If Target.Value = 1 Then
        Call SetRecipients
        End If
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
justenditall:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Sub SetRecipients()
    MsgBox "Second One Runs"
End Sub