Dan Swartz
Board Regular
- Joined
- Apr 17, 2020
- Messages
- 53
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
'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