Run Multiple Private Sub Worksheet_Change(ByVal Target As Range)

[SaraWitch]

Hello peeps,

I'm trying to find a general rule of thumb when wanting to run more than one Private Sub Worksheet_Change(ByVal Target As Range) routines. I have tried a variety of ways, but nothing is working for me.

My two codes are...

Jump to target cell when data entered:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Range("X7:X10000"), Target) Is Nothing Then Exit Sub
  Target.Offset(0, -8).Select
End Sub

Allow more than one option in DVL:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Value_Old As String
  Dim Value_New As String
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, ActiveSheet.Range("H6:H100000")) Is Nothing Then
  Application.EnableEvents = False
  Value_New = Target.Value
  On Error Resume Next
  Application.Undo
  On Error GoTo 0
  Value_Old = Target.Value
  If InStr(Value_Old, Value_New) Then
  If InStr(Value_Old, ",") Then
  If InStr(Value_Old, ", " & Value_New) Then
  Target.Value = Replace(Value_Old, ", " & Value_New, "")
  Else
  Target.Value = Replace(Value_Old, Value_New & ", ", "")
End If
 Else
  Target.Value = ""
End If
 Else
  If Value_Old = "" Then
  Target.Value = Value_New
 Else
  If Value_New = "" Then
  Target.Value = ""
 Else
  If InStr(Target.Value, Value_New) = 0 Then
  Target.Value = Value_Old & ", " & Value_New
End If
End If
End If
End If
  Application.EnableEvents = True
 Else
Exit Sub
End If
End Sub

Any help would be appreciated

 

[Joe4]

You can add your first one to the second one, as long as you just change how it works a little. That section is in red.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim Value_Old As String
  Dim Value_New As String

  If Not Intersect(Range("X7:X10000"), Target) Is Nothing Then
      Target.Offset(0, -8).Select
  End If

  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, ActiveSheet.Range("H6:H100000")) Is Nothing Then
  Application.EnableEvents = False
  Value_New = Target.Value
  On Error Resume Next
  Application.Undo
  On Error GoTo 0
  Value_Old = Target.Value
  If InStr(Value_Old, Value_New) Then
  If InStr(Value_Old, ",") Then
  If InStr(Value_Old, ", " & Value_New) Then
  Target.Value = Replace(Value_Old, ", " & Value_New, "")
  Else
  Target.Value = Replace(Value_Old, Value_New & ", ", "")
End If
 Else
  Target.Value = ""
End If
 Else
  If Value_Old = "" Then
  Target.Value = Value_New
 Else
  If Value_New = "" Then
  Target.Value = ""
 Else
  If InStr(Target.Value, Value_New) = 0 Then
  Target.Value = Value_Old & ", " & Value_New
End If
End If
End If
End If
  Application.EnableEvents = True
 Else
Exit Sub
End If

End Sub

 

[SaraWitch]

That's grand; thank you so much, Joe! I think I tried every variation except this one (my coding knowledge is fairly limited unfortunately).

So, there's not really a general rule of thumb combining routines as it depends on the coding then...? I was hoping there would be an easy "ADD" type function!

 

[Joe4]

The rule of thumb of this:
- You cannot have multiple procedures in the same module with the same name
- If you have multiple things you need to happen in an Event Procedure, they need to all be combined into one single procedure. You can usually do this by "stacking" the code inside the procedure.
- Be aware of any "Exit Sub" sections in your code! If you exit the sub in your first block of code, you will never get to the second block! So you need to change the logic of that statement.

In your code, you had the first IF that said:
"If the update is NOT in a certain range, exit the sub"
So, we needed to change the logic to say:
"If the update IS in a certain range, then do these steps."

Hope that helps!

 

[SaraWitch]

That really does help; again, thank you so much, Joe!

 

[Joe4]

You are welcome.
Glad I was able to help!