Macros not triggered after drop down list selection

[abstracto]

Hello everyone

Glad to join the forum!

I have an issue with macros in an excel file I'm working on. I attached worsheet screen shots for reference.
Sheet 'calculation has all the formulas'. Sheet ' drop down list info' has info for drop down lists (2 used in the main sheet).

There's a drop down list in the cell D6 that has 3 options: SEA, AIR and SEA+AIR.

I created 3 macros that should be triggered based on option selection from above mentioned drop down list.

SEA and AIR macro basically just hides selected rows. SEA+AIR macro unhides all. Each macro also goes back to cell D6 at the end.

However it doesn't seem to work at all. I have checked several forums but couldn't find a solution to my problem.

If I run macros manually everything works well.

Please advise what kind of mistake I did. I must say that I have completely no idea about VB and all the cose used in macros comes from recording macros step by step and then adjusting it a bit based on what I found online.

Thanks in advance for your kind help!

Macros code below:

Sub SEA()
'
' SEA Macro
'

'
    Rows("14:20").Select
    Selection.EntireRow.Hidden = True
    Rows("7:13").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select
    
End Sub

Sub AIR()
'
' AIR Macro
'

'
    Rows("7:13").Select
    Selection.EntireRow.Hidden = True
    Rows("14:20").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select

End Sub
 
Sub BOTH()
'
' BOTH Macro
'

'
    Sheet.Select
    Selection.Sheet.Hidden = False
    Range("D6").Select

End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Macros not triggered after drop down list selection - OzGrid Free Excel/VBA Help Forum

Hello everyone Glad to join the forum! I have an issue with macros in an excel file I'm working on. I attached worsheet screen shots for reference. Sheet 'calculation has all the formulas'. Sheet ' drop down list info' has info for drop down lists (2…

forum.ozgrid.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[abstracto]

Hi RoryA

Thanks for your message!

Well noted on cross-posting. I'll follow your advice in the future definitely!
Just trying to reach wider audience for a higher chance of help if possible.

 

[rollis13]

You need some code to trigger when you make a change in cell D6. Paste this event macro to your sheet's module. Then consider that you have an issue in your BOTH macro but I will leave this up to you as 'homework'.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D6")) Is Nothing Then Exit Sub
    Select Case Target
        Case Is = "SEA"
            Call SEA
        Case Is = "AIR"
            Call AIR
        Case Is = "SEA+AIR"
            Call BOTH
    End Select
End Sub

 

[abstracto]

Thanks a lot rollis13!!!
I really appreciate your help!
I'll do my homework as well as I can!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help. Since I have yet to do my daily BoyScout good deed, here:

Sub BOTH()
    '
    ' BOTH Macro
    '
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select
End Sub

 

[abstracto]

So I pasted above code and still nothing happens

(I haven't addressed Both yet.)

It looks like below now:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D6")) Is Nothing Then Exit Sub
    Select Case Target
        Case Is = "SEA"
            Call SEA
        Case Is = "AIR"
            Call AIR
        Case Is = "SEA+AIR"
            Call BOTH
    End Select
End Sub
Sub SEA()
' SEA Macro
    Rows("14:20").Select
    Selection.EntireRow.Hidden = True
    Rows("7:13").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select
    
End Sub
Sub AIR()
' AIR Macro
    Rows("7:13").Select
    Selection.EntireRow.Hidden = True
    Rows("14:20").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select

End Sub
Sub BOTH()
' BOTH Macro
    Sheet.Select
    Selection.Sheet.Hidden = False
    Range("D6").Select

End Sub

 

[rollis13]

Sorry, in post #4 I said that (at least) the event Worksheet_Change macro must go in the sheet's module (sheet Calculation).

 

[abstracto]

Thanks!

It looks like this now (attached)

Module 1

Sub SEA()
' SEA Macro
    Rows("14:20").Select
    Selection.EntireRow.Hidden = True
    Rows("7:13").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select
    
End Sub
Sub AIR()
' AIR Macro
    Rows("7:13").Select
    Selection.EntireRow.Hidden = True
    Rows("14:20").Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select

End Sub
Sub BOTH()
    '
    ' BOTH Macro
    '
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("D6").Select
End Sub

Module 2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D6")) Is Nothing Then Exit Sub
    Select Case Target
        Case Is = "SEA"
            Call SEA
        Case Is = "AIR"
            Call AIR
        Case Is = "SEA+AIR"
            Call BOTH
    End Select
End Sub

Or I mixed something up?

 

[rollis13]

No, not in Module2, move it to Sheet1 (calculation). That's where you have your cell D6 with it's dropdown list that has to trigger an event macro. when it changes.