VBA Month Button Set

[ianb]

Hi All,

I have a VBA program with 12 Buttons. each button is linked to showing a month in a spreasheet with various data...

e.g. 1 = November
2= December
3= January

I would like to when the spreadsheet is opened to :

select the button for THIS MONTH.

Each button is linked to a macro so it would be to select the macro for the correct month.

If August (NOW) then select Macro (Button 10).
The origional coder did not name the macros by month name.

Does any one have a program to do this. Thanks.

 

[Hui]

It would be a lot simpler if you could attach your file ?

 

[NARAYANK991]

Hi Ian ,

When you have several buttons , and you wish to execute the same process with different parameters , you can use the Application.Caller property to identify the object which was clicked , and hence the month. If the process for every month is the same , you can have just one macro , which can be called with a different parameter value for each month.

Narayan

 

[ianb]

Thank you both for the tips. I will need to obtain the file and can upload the code.

 

[ianb]

Demo File as requested.

 

[betsig250]

I just did something like this earlier this week. Below is the code I used as there are 7 sections where a user can insert a row. So depending on which button is pushed I insert a row in that respective area.

Private Sub Insert_Row()
Dim bName, cName As String
bName = Application.Caller
Call Unprotect

'Inserting a row based on which Insert Button was pushed
Select Case bName
    Case "bFabric"
        cName = "SubFabric"
      
    Case "btrim"
        cName = "SubTrim"
  
    Case "bArtwork"
        cName = "SubArtwork"
  
    Case "bPack"
        cName = "SubPack"
  
    Case "bMisc"
        cName = "SubMisc"
  
    Case "bLeftOver"
        cName = "SubLeftOver"
  
    Case "bComments"
        cName = "Comments"
End Select

    Application.GoTo Reference:=cName
    ActiveCell.Offset(-1, 0).Select
    Selection.EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False

Call Protect
End Sub

 

[ianb]

This is where I am up to at present. A1 has Today() in format mmmm = August

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckMon As Date
CheckMon = Range("A1").Value
If CheckMon = August Then
Call macro1
Else
If CheckMon = September Then
Call macro2
Else
If CheckMon = October Then
Call macro3

End If
End If
End If
End Sub


Sub macro1()
MsgBox ("August")
End Sub
Sub macro2()
MsgBox ("September")
End Sub
Sub macro3()
MsgBox ("October")
End Sub

 

[ianb]

My Final Attempt !!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserChoice As Date
On Error Resume Next
UserChoice = Range("A1").Value
On Error GoTo 0
'Setup the select.
Select Case UserChoice
Case 1
Application.Run "macro1"
Case 2
Application.Run "macro2"
Case 3
Application.Run "macro3"
Case 4
Application.Run "macro4"
Case 5
Application.Run "macro5"
Case 6
Application.Run "macro6"

Case Else

MsgBox "No valid month found !"
Exit Sub

End Select
End Sub

 

[ianb]

Hi can any one improve on this. I think this is the solution.

 

[NARAYANK991]

Hi Ian ,

See this file. Click on the buttons to see the message.

Narayan

 

[ianb]

Hi Naray, Thanks for the feedback. I have my solution and also I have now learnt about Application.Callerwhich will be usful for another project I am working on. many thanks once again.

 

[shrivallabha]

How much do the monthwise code differ from each other is more important from coding maintenance perspective.

If I could create a single procedure which takes argument for month and processes it then I would go for it.

e.g.

Sub MyMonthMacro(strMonth As String)
 MsgBox (strMonth)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Text
  Case "August", "September", "October", "November", "December", "January"
  MyMonthMacro Range("A1").Text
Case Else
  MsgBox "No valid month found !"
End Select
End Sub

 

[ianb]

Perfect..... My program in compact form !!! thank you all. great work... kind regards as always.