# Macros not triggered after drop down list selection



## abstracto (Wednesday at 3:23 AM)

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 (Wednesday at 3:48 AM)

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 (Wednesday at 3:54 AM)

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 (Wednesday at 9:32 AM)

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 (Thursday at 5:31 AM)

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


----------



## rollis13 (Thursday at 5:38 AM)

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 (Thursday at 5:38 AM)

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 (Thursday at 5:42 AM)

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


----------



## abstracto (Thursday at 6:05 AM)

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 (Thursday at 8:43 AM)

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.


----------

