Attaching a different macro to each item on a drop down list

[[email protected]]

I wrote 12 macros to display 1 selected month of the year, instead of displaying 12 months on all worksheets. I then created a drop down list for each month. However, I am having a problem attaching separate macros to each month on the drop down list?

Any help would be greatly appreciated.

[Norie]

You can only attach a macro to the actual dropdown, not it's values.

What you could do though is write code that takes the value selected in the dropdown and then calls the appropriate macro.

Another possibility might be to replace the 12 macros with one macro and assign that macro to the dropdown.

Whether that's possible would depend on what the 12 macros do and whether they are similar.

[[email protected]]

What code would I write to take the value in the drop down list? I am trying to learn VB, but still in the early stages. The spreadsheets I have are for every department and the macros hide the columns to show just the current month financial data. Therefore each macro is different and is named as Jan, Feb, Mar etc. Any help would be great.

[Norie]

Can you post a couple of the subs you have now?

Also, where is the dropdown located and how was it created?

PS Are you sure the subs aren't basically the same? Perhaps with just one difference, the month(s) to hide/show.

[JOHN H. DAVIS]

If for instance your drop down list was in Range("A1") then maybe something like this?

Please Login or Register  to view this content.

[[email protected]]

Sub May()
'
' May Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Columns("M:AB").Select
Selection.EntireColumn.Hidden = True
Range("L6").Select
Sheets("Faculty Summary").Select
Cells.Select
Selection.EntireRow.Hidden = False
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 3
Columns("K:Z").Select
Selection.EntireColumn.Hidden = True
Range("J6").Select
Sheets("Sch 7").Select
Cells.Select
Selection.EntireRow.Hidden = False
Columns("M:AB").Select
Selection.EntireColumn.Hidden = True
Range("L5").Select
Sheets("Cost Centers").Select
Cells.Select
Selection.EntireRow.Hidden = False
Columns("L:AA").Select
Selection.EntireColumn.Hidden = True
Range("K6").Select
Sheets("Faculty Expenses by Acct Exp").Select
Range("L6").Select
End Sub
Sub Jun()
'
' Jun Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Sheets("Faculty Summary").Select
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Sheets("Sch 7").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Sheets("Cost Centers").Select
Cells.Select
Selection.EntireColumn.Hidden = False
Sheets("Faculty Expenses by Acct Exp").Select
Columns("L:L").Select
Selection.EntireColumn.Hidden = True
Columns("N:AB").Select
Selection.EntireColumn.Hidden = True
Range("M6").Select
Sheets("Faculty Summary").Select
Columns("J:J").Select
Selection.EntireColumn.Hidden = True
Columns("L:Z").Select
Selection.EntireColumn.Hidden = True
Range("K6").Select
Sheets("Sch 7").Select
Columns("L:L").Select
Selection.EntireColumn.Hidden = True
Columns("N:AB").Select
Selection.EntireColumn.Hidden = True
Range("M5").Select
Sheets("Cost Centers").Select
Columns("K:K").Select
Selection.EntireColumn.Hidden = True
Columns("M:AA").Select
Selection.EntireColumn.Hidden = True
Range("L6").Select
Sheets("Faculty Expenses by Acct Exp").Select
Range("C4").Select
End Sub

[[email protected]]

Can you post a couple of the subs you have now?

Also, where is the dropdown located and how was it created?

PS Are you sure the subs aren't basically the same? Perhaps with just one difference, the month(s) to hide/show.

Apologies for the delay in responding,
I have just posted the code. The drop down data validation list is located in cell c4 on sheet Faculty expenses by Account Exp.