vba 在一张工作表中运行多个“私有子工作表_更改（ByVal Target As Range）”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21658210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running multiple 'Private Sub Worksheet_Change(ByVal Target As Range)' in one worksheet
提问by user3111354
I have tried to merge 2 codes into one Private Sub and while the first one runs fine the second is not being pick up at all. It does not return any errors it just does not call the required Sub. Any help will be appreciated.
我试图将 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
回答by Siddharth Rout
Your code has Nextwhich is not required. And you are missing End If. I am surprised that the code is running at all to execute the first IF/ENDIF
您的代码有Next哪些不是必需的。而你却不见了End If。我很惊讶代码根本没有运行以执行第一个 IF/ENDIF
This works for me (Tried and Tested)
这对我有用（久经考验）
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
