如何组合多个工作表更改事件Excel VBA

Question

我需要将以下3个子例程组合成单个工作表更改事件，但我不确定如何。

我试过在工作表编辑器和工作簿编辑器中分别编写一个子条目。然而，鉴于我有3个子程序都指向相同的工作表，我不确定如何结合它们。任何帮助都非常感谢!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3:D100")) Is Nothing Then
Exit Sub

Else
Dim i As Integer
For i = 3 To 100
If Range("D" & i).Value = "Remote" Then
Range("O" & i).Value = "N/A"
Range("P" & i).Value = "N/A"
Range("Q" & i).Value = "N/A"

End If
Next i
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("H3:H100")) Is Nothing Then
Exit Sub

Else
Dim e As Integer
For e = 3 To 100
If Range("H" & e).Value = 1 Then
Range("I" & e).Value = "N/A"

End If

Next e
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("I3:I100")) Is Nothing Then
Exit Sub

Else
Dim e As Integer
For e = 3 To 100
If Range("I" & e).Value = 1 Then
Range("H" & e).Value = "N/A"

End If

Next e
End If
End Sub

Answer 1

翻转逻辑

If Intersect(Target, Range("D3:D100")) Is Nothing Then 
Exit Sub
Else
...
End If

更改为

If Not Intersect(Target, Range("D3:D100")) Is Nothing Then 
' Remove Exit Sub
' Remove Else
...
End If

对其他两个Intersect调用执行相同的操作，然后将所有内容合并到一个Worksheet_Change处理程序中。

很可能你也想禁用事件，以避免在写入工作表时重新触发事件:

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

' Your three Intersect checks
SafeExit:
Application.EnableEvents = True
End Sub

Answer 2

试试这个。把它放在工作表中，而不是工作簿

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
c = 1
Else
If Not Intersect(Target, Range("H3:H100")) Is Nothing Then
c = 2
Else
If Not Intersect(Target, Range("I3:I100")) Is Nothing Then
c = 3
End If
End If
End If
Select Case c
Case 1
' your stuff
Case 2
'your stuff
Case 3
'your stuff
Case Else
End Select
End Sub