# Run a Macro from List Box selection



## Craig2009 (Sep 28, 2009)

Hi Guys I need help with running a Macro from a selection in a List Box well actual I want to run one of about a dozen from the dozen selctions.

My VB skills are not that great and I usually make my Macros by recording them. 

I have managed to make the List Box using Data Validation and a range of cells C279:C295. The List Box is located in Cell E5. I want to be able to select one of the selections in the list and then simply have it run the corresponding Macro. Could someone show me an example Code? The list contains: 2 Days, 3 Days, 4 days etc. The Macros would be named Macro2, Macro3, Macro4 etc numbers correspond for ease.


----------



## Rollin_Again (Sep 4, 2003)

Right click on the sheet name at the bottom of the workbook and choose "VIEW CODE" and then paste the code below into the blank code module.


```
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$5" Then

'Your Code Here

End If

End Sub
```
If you are planning on running multiple macros using the same event you would probably be better off using a *SELECT CASE* statement to evaluate which cell was changed instead of using a separate *IF* statement to check each condition. Just replace the messagebox with your own macro code and also change the cell addresses next to each case statement.


```
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$E$5"

MsgBox ("E5")

Case "$F$5"

MsgBox ("F5")

Case "$G$5"

MsgBox ("G5")

End Select

End Sub
```
Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

If the msgbox's will always be the same as the Target.Address, you could simplify to...

```
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
    Case "$E$5", "$F$5", "$G$5"
        MsgBox Target.Address
    End Select
End Sub
```


----------



## Craig2009 (Sep 28, 2009)

Hi Zack,
Thanks for replying. I appreciate any help you can give. I'm not sure that was what I was after or maybe I just have no real idea what I'm actually acfter.

What I want is a ort of code that would be something like this(excuse how lame it may look)

If E5 = "3 days" run macro 3
If E5 = "4 days" run macro 4 
and so on up to 12 days and macro 12

What the Macro does is rearrange a spreadsheet. The standard spreadsheet I have made is for recording information and is set out as 3 rows of 5 days. The totals are then recorde at the bottom. The macro for 3 days would change the formats on days 4 and 5 so that the font is white no borders or shading etc so they kind of disappear and also hide the two rows of five futher down so that the totals appear right beneath the 3 days. I've pasted the macro code below for you to see. As mentioned I recorde macros so it's quite long. The macro for 4 days would leave the 4 daily totals areas visible and so on. So I want to select the number of days from the dropdown list and have it run the corresponding macro. The dropdown list runs from E5 and the range it selects from is C279:C295 C279 being blank and 3 Days in C280 4 days in C281 etc etc.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 28/09/2009 by 460
'
'
ActiveWindow.SmallScroll Down:=132
Rows("133:154").RowHeight = 0
Rows("132:132").RowHeight = 9
ActiveWindow.SmallScroll Down:=-39
Rows("98:132").RowHeight = 0
Rows("97:97").RowHeight = 1.5
ActiveWindow.SmallScroll Down:=-36
Rows("62:97").RowHeight = 0
Rows("61:61").RowHeight = 11.25
ActiveWindow.SmallScroll Down:=-24
Rows("57:61").RowHeight = 0
Rows("56:56").RowHeight = 0
ActiveWindow.SmallScroll ToRight:=13
ActiveWindow.SmallScroll Down:=-3
Range("Q6:Y54").Select
Range("Q54").Activate
Selection.Interior.ColorIndex = xlNone
ActiveWindow.LargeScroll Down:=-1
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Font.ColorIndex = 2
ActiveWindow.SmallScroll Down:=-39
ActiveWindow.LargeScroll ToRight:=-1
Range("C8").Select
End Sub


----------



## Rollin_Again (Sep 4, 2003)

Just use a select case statement like the one below. Just follow my previous instruction to right click on the sheet name at the bottom of the workbook and choose *VIEW CODE* and paste the code below into the blank module. I've only included 4 case conditions so you just need to add the rest using the same format.


```
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$5" Then

Select Case Target.Value

Case "3 days"
Call Macro3

Case "4 days"
Call Macro4

Case "5 days"
Call Macro5

Case "6 days"
Call Macro5

End Select

End If

End Sub
```
Regards,
Rollin


----------



## Craig2009 (Sep 28, 2009)

Hi Rollin,

Thanks for your help. The code looks good but will I need a command button or something to run it rather than it just running when I make a selection from the drop down list? When I paste it into the "View Code" It tries to just run straight away when I save it and get away from the editing box. It oviously finds an error as i haven't added the extra Macros yet. But if I delete the excess "Case" values and macros it still doesn't run just from the drop down list selection.


----------



## Rollin_Again (Sep 4, 2003)

So you are saying that the code is not firing when you change the value in cell B5? Can you post your modified code and if possible upload your sample workbook?

Regards,
Rollin


----------



## Craig2009 (Sep 28, 2009)

I've uploaded a sample of the workbook. It has just the two macros I made by recording one for 3 days and one for 15 days. There is no code in the view code. There are two macro buttons. one to set up the sheet for 3 days and the other to take it back to 15 days. I want to be able to just select 3 days from the dropdown list and it fires the Macro 3 and then if I select 15 days it would go back to 15 day set up.


----------



## Rollin_Again (Sep 4, 2003)

According to your very first post the data validation dropdown is to be located in Cell E5. When I looked at your sample workbook it appears to be in Cell E3. Did you remember to change the code to reflect the correct cell to evaluate? Obviously the portion in red would need to be changed to the number 3.

*If Target.Address = "$E$5" *

Regards,
Rollin


----------



## Craig2009 (Sep 28, 2009)

Hi Rollin,

Yes, sorry, I did change the code to reflect E3. I figured no point to correct after a few threads as I could just edit the number. I also posted the workbook but forgot to change the assigned macros on the buttons that show the example macros. I renamed the Macros macro3 and macro15 but the buttons have macro1 and macro2 assigned to them.


----------

