How to combine multiple worksheet Change events Excel VBA-CodePudding

I need to combine the following 3 subroutines into a single worksheet change event but I am unsure how.

I have tried writing one sub in the worksheet editor and another in the workbook editor. However given that I have 3 subroutines all referring to the same worksheet, I am unsure how to combine them. Any help is greatly appreciated!

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

CodePudding user response：

Flip the logic.

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

Change this to

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

Do the same for the two other Intersect calls and then combine everything into one Worksheet_Change handler.

Most likely you want to disable events as well, to avoid re-triggering the event when writing to the sheet:

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

CodePudding user response：

try this. put this in the worksheet, not the workbook

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