CODEWITHSUNDEEP
vbaexcel
Chris Efford
Chris Efford

Reputation: 1

Excel 2013 - Using multiple Private Sub commands in one worksheet

I use the following code to run one of my macro's depending on the item chosen in the dropdown field, Below is the code i use:

This works perfectly except i would like to repeat this process again below the first. It simply pastes information based on the answer in the dropdown field. I would like to do this again in Cell A136 - any help would be appreciated, whenever i try i get errors and or it does nothing.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A43")) Is Nothing Then
Select Case Range("A43")
    Case "Amputation": Amputation
    Case "Asthma": Asthma
    Case "Burns_No_Smoke_Inhalation_Not_On_Limb": Burns_No_Smoke_Inhalation_Not_On_Limb
    Case "Burns_No_Smoke_Inhalation_On_Limb": Burns_No_Smoke_Inhalation_On_Limb
    Case "Burns_Smoke_Inhalation_Not_On_Limb": Burns_Smoke_Inhalation_Not_On_Limb
    Case "Burns_Smoke_Inhalation_On_Limb": Burns_Smoke_Inhalation_On_Limb
    Case "Closed_Fracture": Closed_Fracture
    Case "CORD": CORD
    Case "CPR_Patient_Does_Not_Recover": CPR_Patient_Does_Not_Recover
    Case "CPR_Patient_Does_Recover": CPR_Patient_Does_Recover
    Case "Dislocation": Dislocation
    Case "Hyperthermia": Hyperthermia
    Case "Hypothermia": Hypothermia
    Case "Open_Fracture_Major_Bleeding": Open_Fracture_Major_Bleeding
    Case "Open_Fracture_Minor_Bleeding": Open_Fracture_Minor_Bleeding
    Case "Open_Wound_Major_Controlable_Bleeding": Open_Wound_Major_Controlable_Bleeding
    Case "Open_Wound_Minor_Bleeding": Open_Wound_Minor_Bleeding
    Case "Open_Wound_Needs_Tourniquet": Open_Wound_Needs_Tourniquet
    Case "Spinal_Injury_Conscious_No_KED": Spinal_Injury_Conscious_No_KED
    Case "Spinal_Injury_Conscious_With_KED": Spinal_Injury_Conscious_With_KED
    Case "Spinal_Injury_Unconscious_No_KED": Spinal_Injury_Unconscious_No_KED
    Case "Spinal_Injury_Unconscious_With_KED": Spinal_Injury_Unconscious_With_KED

End Select     
End If
End Sub

Upvotes: 0

Views: 313

Answers (1)

barrowc
barrowc

Reputation: 10679

You can simplify that code greatly by replacing the whole Select Case statement with:

Application.Run Range("A43").Value

then your event handler would become:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A43")) Is Nothing Then
    Application.Run Range("A43").Value
ElseIf Not Intersect(Target, Range("A136")) Is Nothing Then
    Application.Run Range("A136").Value
End If

End Sub

For a situation where you needed to do more work than just calling another macro then you could put the whole Select Case part in a separate function and pass the relevant cell to it as a Range parameter

Upvotes: 4

Related Questions