same codes for same command buttons procedures

[Amin Kaveh]

Hi
I am designing a calendar with 365 command button as 365 days, and all buttons has same codes in spite of indexes.
Is there any way to have one procedure instead of 365. for example my first day on seventh month procedure is here:

Private Sub day_7_1_Click()
For i = 1 To Sheets.Count
If Worksheets(i).Name = Right(Daily_Report.main_year.Text, 2) & ".07.01" Then
Call Daily_Report.CommandButton_edit_Click
End If
Next i
Daily_Report.Text1_date = Daily_Report.main_year.Value
Daily_Report.Text2_date = "07"
Daily_Report.Text3_date = "01"
End Sub

Thanking you in advance

 

[Domenic]

Note: The following solution has not been fully tested.
​

You can use a class module with one event handler to handle all of your commandbuttons. First, insert a class module (Visual Basic Editor >> Insert >> Class Module). Then make visible the Properties Window (View >> Properties Window), if it isn't already so. Then, in the Properties Window under the name property, enter clsCommandButton. Then, copy/paste the following code into the code module for the class...

Option Explicit

Public WithEvents myCommandButton As MSForms.CommandButton


Private Sub myCommandButton_Click()


    Dim sa() As String
    Dim sYear As String
    Dim sMonth As String
    Dim sDay As String
    Dim i As Long
    
    sa = Split(myCommandButton.Name, "_")
    
    sYear = Daily_Report.main_year.Text
    sMonth = Format(sa(1), "00")
    sDay = Format(sa(2), "00")
        
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = Right(sYear, 2) & "." & sMonth & "." & sDay Then
            Call Daily_Report.CommandButton_edit_Click
        End If
    Next i


    Daily_Report.Text1_date = sYear
    Daily_Report.Text2_date = sMonth
    Daily_Report.Text3_date = sDay
    
End Sub

Then, in the code module for the userform, copy/paste the following code...

Option Explicit

Dim colCommandButtons As New Collection


Private Sub UserForm_Initialize()
    
    Dim cCommandButton As clsCommandButton
    Dim ctrl As MSForms.Control
       
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            Set cCommandButton = New clsCommandButton
            Set cCommandButton.myCommandButton = ctrl
            colCommandButtons.Add cCommandButton
        End If
    Next ctrl
    
End Sub

Hope this helps!

 

[Amin Kaveh]

Thank you very much
I did what you said and run the code
but it countered with error in line "colCommandButtons.Add cCommandButton" with (run time erroe 424,object required)

 

[Domenic]

Have a look at a sample workbook...

https://1drv.ms/x/s!Atmin3kqXqL0bT3NbU65qWiaN9Q

Note that for demonstration purposes I've only included commandbuttons for 3 days, and a commandbutton to unload the userform.

 

[Amin Kaveh]

Thanks alot. Its work.
but I have another command buttons in same form that dosen't work anymore.
Would you please also instruct me on this?
Thank you very much.

 

[Domenic]

You're very welcome!

How are your other command buttons named? And how many of them do you have?

 

[Amin Kaveh]

I mean that can we have two different class module for two different command buttons group?
For example I have 12 command buttons for 12 month that as same as days we have same procedure for them.
The days and month command butons have same userform.
My seventh months procedure is here:

Private Sub month_7_Click()
Frame_7.Visible = True
frame_2.Visible = False
frame_3.Visible = False
Frame_4.Visible = False
Frame_5.Visible = False
Frame_6.Visible = False
frame_1.Visible = False
Frame_8.Visible = False
Frame_9.Visible = False
Frame_10.Visible = False
Frame_11.Visible = False
Frame_12.Visible = False
month_7.BackColor = &H8080FF
month_2.BackColor = &H8000000F
month_3.BackColor = &H8000000F
month_4.BackColor = &H8000000F
month_5.BackColor = &H8000000F
month_6.BackColor = &H8000000F
month_1.BackColor = &H8000000F
month_8.BackColor = &H8000000F
month_9.BackColor = &H8000000F
month_10.BackColor = &H8000000F
month_11.BackColor = &H8000000F
month_12.BackColor = &H8000000F
End Sub

Thanking you in advance

 

[Domenic]

In that case, try the following instead...

1) Insert a class module (Insert >> Class Module), name the module clsDayCommandButton, and then copy/paste the following code into the code module...

Option Explicit

Public WithEvents DayCommandButton As MSForms.CommandButton


Private Sub DayCommandButton_Click()
    MsgBox DayCommandButton.Name
End Sub

2) Insert another class module (Insert >> Class Module), name the module clsMonthCommandButton, and then copy/paste the following code into the code module...

Option Explicit

Public WithEvents MonthCommandButton As MSForms.CommandButton


Private Sub MonthCommandButton_Click()
    MsgBox MonthCommandButton.Name
End Sub

3) In the code module for the userform, copy/paste the following...

Option Explicit

Dim colCommandButtons As New Collection


Private Sub UserForm_Initialize()
    Dim cDayCommandButton As clsDayCommandButton
    Dim cMonthCommandButton As clsMonthCommandButton
    Dim ctrl As MSForms.Control
    
    For Each ctrl In Me.Controls
        If LCase(Left(ctrl.Name, 3)) = "day" Then
            Set cDayCommandButton = New clsDayCommandButton
            Set cDayCommandButton.DayCommandButton = ctrl
            colCommandButtons.Add cDayCommandButton
        ElseIf LCase(Left(ctrl.Name, 5)) = "month" Then
            Set cMonthCommandButton = New clsMonthCommandButton
            Set cMonthCommandButton.MonthCommandButton = ctrl
            colCommandButtons.Add cMonthCommandButton
        End If
    Next ctrl
End Sub

Hope this helps!

 

[Amin Kaveh]

Thank you very much. you spend alot on this thread.
By the last question, would you please recommend one procedure for 12 months.
seventh month procedure has been mentioned on previous post.

 

[Amin Kaveh]

I mean that can we have two different class module for two different command buttons group?
For example I have 12 command buttons for 12 month that as same as days we have same procedure for them.
The days and month command butons have same userform.
My seventh months procedure is here:

Private Sub month_7_Click()
Frame_7.Visible = True
frame_2.Visible = False
frame_3.Visible = False
Frame_4.Visible = False
Frame_5.Visible = False
Frame_6.Visible = False
frame_1.Visible = False
Frame_8.Visible = False
Frame_9.Visible = False
Frame_10.Visible = False
Frame_11.Visible = False
Frame_12.Visible = False
month_7.BackColor = &H8080FF
month_2.BackColor = &H8000000F
month_3.BackColor = &H8000000F
month_4.BackColor = &H8000000F
month_5.BackColor = &H8000000F
month_6.BackColor = &H8000000F
month_1.BackColor = &H8000000F
month_8.BackColor = &H8000000F
month_9.BackColor = &H8000000F
month_10.BackColor = &H8000000F
month_11.BackColor = &H8000000F
month_12.BackColor = &H8000000F
End Sub

Thanking you in advance

Thank you very much. you spend alot on this thread.
By the last question, would you please recommend one procedure for 12 months.
seventh month procedure has been mentioned on previous post.