Multiple VBA Private Sub Worksheet_Change(ByVal Target As Range)

[Dan Swartz]

I'm not sure how to even ask this question as I'm not done with my code yet. but it appears that I can only run 2 "Private Sub Worksheet_Change(ByVal Target As Range)" at a time. I'm hoping there is some code to tie this together.

I have a sheet where everything will be entered in column E. In column E, I would like to have most likely 10 different Private subs that run when something is changed in column E. All of these are tied to specific cells or specific actions, but none of them is dependant on the others. Is there a way to do this? On this sheet, I have a lot of proprietary information. So just uploading the sheet here is not an option.

I have the following codes and all work great individually. There are 4 of them, however, it will only run whatever the first two are.

'This section is for wood Species. It changes what is in the  to a number after species is selected
If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 5 Then
      WoodSpecies = Target.Value
      WoodSpeciesNumber = Application.VLookup(WoodSpecies, Sheet20.Range("WoodSpeciesClazakNumber"), 2, False)
      If Not IsError(WoodSpeciesNumber) Then
         On Error GoTo Xit
         Application.EnableEvents = False
         Target.Value = WoodSpeciesNumber
      End If
   End If
Xit:
   Application.EnableEvents = True
 
   
'This is for crown Molding Selection
Dim Answer As VbMsgBoxResult
Dim Trim As Range
Set Trim = Range("E27")
   
    If Intersect(Target, Trim) Is Nothing Then Exit Sub 
    If Target.Value > 0 Then
       
        Answer = MsgBox("Is this the default KL-314 Crown Molding?", vbQuestion + vbYesNo + vbDefaultButton2, "Valve Type")
       
    If Answer = vbYes Then
        Range("G27").Value = ("KL-314")
     
        ElseIf Answer = vbNo Then
       Range("G27").Value = ("Other")
   
    End If
End If
 
 
'This is for getting the correct rough-in with trim selection
Dim Answer As VbMsgBoxResult
Dim Trim As Range
Set Trim = Range("E287")
   
    If Intersect(Target, Trim) Is Nothing Then Exit Sub
    If Target.Value > 0 Then
       
        Answer = MsgBox("Is this an Integrated Valve?", vbQuestion + vbYesNo + vbDefaultButton2, "Valve Type")
       
    If Answer = vbYes Then
      'Range("G287").Value = ("R20000 + R11000")
        Sheet38.Range("A4:A5").Clear
        Sheet38.Range("A4").Value = ("R11000")
        Sheet38.Range("A5").Value = ("R20000")
     
        ElseIf Answer = vbNo Then

      'Range("G287").Value = ("R10000")
       Sheet38.Range("A5").Clear
       Sheet38.Range("A4").Value = ("R10000")
  
    End If
End If


'Hardware Color Selecction
Dim ColorRange As Range

Set ColorRange = Range("E113:E126")

    If Target.Cells.Count > 1 Then Exit Sub
 
    If Not Application.Intersect(Target, ColorRange) Is Nothing Then
        CabinetHardwareUF.Tag = Target.Address(, , , True)
        CabinetHardwareUF.Show

End If

 
End Sub

 

[steve the fish]

You havent given us the entire code. You can only have one worksheet change event in any one worksheet module. Id imagine from first glance that these lines are the problem:

If Target.CountLarge > 1 Then Exit Sub

If Intersect(Target, Trim) Is Nothing Then Exit Sub

You dont want the exit the sub as you want the next code block to execute so use something like:

If Not Intersect(Target, Trim) Is Nothing Then
'code
End If

Essentially if the conditions are met/not met then you want to execute sections of code or skip them and this can enable that. Try it out and if get stuck come back.

 

[Dan Swartz]

'

 

[Dan Swartz]

You havent given us the entire code. You can only have one worksheet change event in any one worksheet module. Id imagine from first glance that these lines are the problem:

If Target.CountLarge > 1 Then Exit Sub

If Intersect(Target, Trim) Is Nothing Then Exit Sub

You dont want the exit the sub as you want the next code block to execute so use something like:

If Not Intersect(Target, Trim) Is Nothing Then
'code
End If

Essentially if the conditions are met/not met then you want to execute sections of code or skip them and this can enable that. Try it out and if get stuck come back.

Thanks Steve,
This is the entire code that I have at this time. I will try your suggestion

 

[Dan Swartz]

Steve, That did not work or I didn't put it incorrectly. Code errors out if I change to what you suggested. I'm a beginner/amateur when it comes to VBA. So I'm guessing is something I did.