Help with MACROs - - running macro depending on selection made from dropdown list

[breba]

K. Pretty new to all of this - - all of what I have so far is from researching online but I'm still coming across a problem.....

Goal is to have a macro run based on a selection that is made from a drop down list.
My list will consist of 6 different macros that I would like to run OR what "view" I would like to see with the selection that is made.

For instance, my drop down list will be: Week 1, Week 2, Week 3, Week 4, Week 5, Month ..... and when I select "Week 1" it will run a macro that first will un-hide all rows in the worksheet (bc if the current view has rows hidden they might need to be un-hidden for this particular view) then hide certain rows (that are predetermined) that are specific to the "Week 1" view. So then when I select that I would like to view "Week 3" it will show me only Week 3.

Note: I originally had assigned the macros to buttons and they worked perfectly, however, now that I added a list it's not working and Im not 100% sure of what I'm doing (regarding a combo box)? Again, I just pulled things from research I did online, Im not extremely knowledgeable in this area.

Please help!
I also have added a seperate macro for a check box to hide/unhide columns and it works perfectly - - it seems, though, when I added that, this is when my other macros stopped working? IDK?? All help would be greatly appreciated!!
Thanks in advance!

I've posted my macros below for easy reference

Sub Week1view()
'
' Week1view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Rows("11:19").Select
ActiveWindow.SmallScroll Down:=12
Range("11:19,29:37").Select
Range("A29").Activate
ActiveWindow.SmallScroll Down:=21
Range("11:19,29:37,47:55").Select
Range("A47").Activate
ActiveWindow.SmallScroll Down:=18
Range("11:19,29:37,47:55,65:73").Select
Range("A65").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-45
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Sub Week2view()
'
' Week2view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Range("9:10,13:19").Select
Range("A13").Activate
ActiveWindow.SmallScroll Down:=12
Range("9:10,13:19,27:28,31:37").Select
Range("A31").Activate
ActiveWindow.SmallScroll Down:=21
Range("9:10,13:19,27:28,31:37,45:46,49:55").Select
Range("A49").Activate
ActiveWindow.SmallScroll Down:=15
Range("9:10,13:19,27:28,31:37,45:46,49:55,63:64,67:73").Select
Range("A67").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-45
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Sub Week3view()
'
' Week3view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Range("9:12,15:19").Select
Range("A15").Activate
ActiveWindow.SmallScroll Down:=12
Range("9:12,15:19,27:30,33:37").Select
Range("A33").Activate
ActiveWindow.SmallScroll Down:=18
Range("9:12,15:19,27:30,33:37,45:48,51:55").Select
Range("A51").Activate
ActiveWindow.SmallScroll Down:=21
Range("9:12,15:19,27:30,33:37,45:48,51:55,63:66,69:73").Select
Range("A69").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-48
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Sub Week4view()
'
' Week4view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Range("9:14,17:19").Select
Range("A17").Activate
ActiveWindow.SmallScroll Down:=15
Range("9:14,17:19,27:32,35:37").Select
Range("A35").Activate
ActiveWindow.SmallScroll Down:=18
Range("9:14,17:19,27:32,35:37,45:50,53:55").Select
Range("A53").Activate
ActiveWindow.SmallScroll Down:=9
Range("9:14,17:19,27:32,35:37,45:50,53:55,63:68,71:73").Select
Range("A71").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-51
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Sub Week5view()
'
' Week5view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Range("9:16,19:19").Select
Range("A19").Activate
ActiveWindow.SmallScroll Down:=12
Range("9:16,19:19,27:34,37:37").Select
Range("A37").Activate
ActiveWindow.SmallScroll Down:=18
Range("9:16,19:19,27:34,37:37,45:52,55:55").Select
Range("A55").Activate
ActiveWindow.SmallScroll Down:=21
Range("9:16,19:19,27:34,37:37,45:52,55:55,63:70,73:73").Select
Range("A73").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-42
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Sub EntireMonthView()
'
' EntireMonthView Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub
------------------
Private Sub ComboBox1_Click()
Select Case ComboBox1.Value

Case "Week 1"
Week1view
Case "Week 2"
Week2view
Case "Week 3"
Week3view
Case "Week 4"
Week4view
Case "Week 5"
Week5view
Case "Month"
EntireMonthView
Case Else
Exit Sub

End Select

End Sub
------------------
Sub HideColumns()
Dim MyRange As Range
Set MyRange = Range("B:F,J:O,H:H,R:V")
MyRange.EntireColumn.Hidden = Not MyRange.EntireColumn.Hidden
End Sub

 

[L. Howard]

Hi breba,

Maybe set it up something like this in the sheet module.
I used cell F1 for a DataValadation Drop Down with a list of the macro names.

Week1,Week2,Week3,Week4,Week5,PrintMoreMoney

Change the drop down cell to suit your sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$1" Or Target.Cells.Count > 1 Then Exit Sub
  If Target = "Week1" Then Week1
  If Target = "Week2" Then Week2
  If Target = "Week3" Then Week3
  If Target = "Week4" Then Week4
  If Target = "Week5" Then Week5
  If Target = "PrintMoreMoney" Then PrintMoreMoney
End Sub

Sub Week1()
MsgBox "Week 1 code"
End Sub

Sub Week2()
MsgBox "Week 2 code"
End Sub

Sub Week3()
MsgBox "Week 3 code"
End Sub

Sub Week4()
MsgBox "Week 4 code"
End Sub

Sub Week5()
MsgBox "Week 5 code"
End Sub

Sub PrintMoreMoney()
MsgBox "Printing more money"
End Sub

Regards,
Howard

 

[breba]

Awesome! Than you so incredibly much! Works exactly how it want it to!
..... I did run into two problems, however, I'm not sure what I have done but ALL of them work except for when I select the view for Week4 and the Month.... Can you help me correct my problem?! Here's what I have for those.... Thanks again!!

Sub Week4view()
'
' Week4view Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Range("9:14,17:19").Select
Range("A17").Activate
ActiveWindow.SmallScroll Down:=15
Range("9:14,17:19,27:32,35:37").Select
Range("A35").Activate
ActiveWindow.SmallScroll Down:=18
Range("9:14,17:19,27:32,35:37,45:50,53:55").Select
Range("A53").Activate
ActiveWindow.SmallScroll Down:=9
Range("9:14,17:19,27:32,35:37,45:50,53:55,63:68,71:73").Select
Range("A71").Activate
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-51
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub

Sub EntireMonthView()
'
' EntireMonthView Macro
'

'
Cells.Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=0
Rows("41:77").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("A5").Select
End Sub


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Or Target.Cells.Count > 1 Then Exit Sub
If Target = "Week1" Then Week1view
If Target = "Week2" Then Week2view
If Target = "Week3" Then Week3view
If Target = "Week4" Then Week4view
If Target = "Week5" Then Week5view
If Target = "Month" Then EntireMonthView
End Sub

Sub Week1()
MsgBox "Week 1 code"
End Sub

Sub Week2()
MsgBox "Week 2 code"
End Sub

Sub Week3()
MsgBox "Week 3 code"
End Sub

Sub Week4()
MsgBox "Week 4 code"
End Sub

Sub Week5()
MsgBox "Week 5 code"
End Sub

Sub EntireMonthView()
MsgBox "Entire month view"
End Sub

 

[L. Howard]

Your change_event macro is calling the macro name Week1View.

If Target = "Week1" Then Week1View

But here is what you have instead, just Week1

Sub Week1()
MsgBox "Week 1 code"
End Sub

Which should be

Sub Week1View()
MsgBox "Week 1 code"
End Sub


These all worked for me.

Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$A$3" Or Target.Cells.Count > 1 Then Exit Sub
 If Target = "Week1" Then Week1View
 If Target = "Week2" Then Week2View
 If Target = "Week3" Then Week3View
 If Target = "Week4" Then Week4View
 If Target = "Week5" Then Week5View
 If Target = "Month" Then EntireMonthView
 End Sub

 Sub Week1View()
 MsgBox "Week 1 code"
 End Sub

 Sub Week2View()
 MsgBox "Week 2 code"
 End Sub

 Sub Week3View()
 MsgBox "Week 3 code"
 End Sub

 Sub Week4View()
 MsgBox "Week 4 code"
 End Sub

 Sub Week5View()
 MsgBox "Week 5 code"
 End Sub

 Sub EntireMonthView()
 MsgBox "Entire month view"
 End Sub

Also, when posting code you should use tags like this. (without the spaces) and paste your code between them.
Then you will get what you see above, which retains spaces and indents etc.
Makes the code a bit easier to read and such.


[ Code ]


[/ Code ]

Regards,
Howard

 

[breba]

Got it corrected - works great!
You're help is appreciated - thank you so much!
Also, thanks for the tip on how to post a code (I was wondering how you did that)

 

[L. Howard]

You're welcome, thanks for the feed back.

Regards,
Howard