none
Run a macro on drop down list selection RRS feed

  • Question

  • I want to be able to run one of 3 macros I created (Cultivator1, TowBehind1 or TwoBetween1) based on one of 3 corresponding selections from my drop down list in T7. I'm using the following code located in ThisWorkbook. It runs the first macro okay but does not run the next two when the appropriate list selection is made. The ElseIf lines don't seem to work. What am I doing wrong?

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        If Not Intersect(Target, Range("T7")) Is Nothing Then
        On Error GoTo FallThrough
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        If Range("T7").Value = "CULTIVATOR" Then _
            Call Cultivator1
        ElseIf Range("T7").Value = "AIR SEEDER (NH3 TOW BEHIND)" Then _
            Call TowBehind1
        ElseIf Range("T7").Value = "AIR SEEDER (NH3 TOW BETWEEN)" Then _
            Call TowBetween1
        End If
        
    FallThrough:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    • Edited by GHW7 Tuesday, December 1, 2015 10:50 PM
    Tuesday, December 1, 2015 8:40 PM

Answers

  • Better to use Select Case like the following instead of ElseIf's.

    Following code has been tested and works. If it fails then ensure that the Validation DropDown has the correct values to match the values in the VBA code. (ie. Correct spelling, no additional or missing spaces. I have modified the code so that it converts the data validation options to uppercase for the comparison but make sure that code has all uppercase in the Case statements.

    See my comments to improve the code.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        
        'Omit following line if this sub called from every worksheet
        'but if only from a particular worksheet then is required
        If Sh.Name <> "Sheet1" Then Exit Sub    'Edit "Sheet1" to your sheet name
       
        If Not Intersect(Target, Range("T7")) Is Nothing Then
            On Error GoTo FallThrough
            Application.EnableEvents = False
            Application.ScreenUpdating = False
           
            Select Case UCase(Target.Value)
               Case "CULTIVATOR"
                   Call Cultivator1
               Case "AIR SEEDER (NH3 TOW BEHIND)"
                   Call TowBehind1
               Case "AIR SEEDER (NH3 TOW BETWEEN)"
                Call TowBetween1
            End Select
        End If
    FallThrough:
        'If using On Error then include MsgBox if error occurred
        'Otherwise you will never know that an error has occurred.
        If Err.Number <> 0 Then
           MsgBox "Error occurred in Private Sub Workbook_SheetChange"
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, December 2, 2015 1:24 AM
    • Marked as answer by GHW7 Wednesday, December 2, 2015 2:10 PM
    Wednesday, December 2, 2015 1:21 AM