How to Create Calendar Using VBA in Excel (with Easy Steps)

If you want to create an Excel VBA calendar, you have come to the right place. Here, we will walk you through some easy and effective steps to do the task smoothly.

Create a Calendar Using VBA in Excel: 10 Easy Steps

In the following article, we will describe 10 easy and effective steps to create an Excel VBA calendar. You can see the overview of our created calendar. Next, we will describe the steps to create such a calendar. Here, we used Microsoft Excel 365. You can use any available Excel version.

Step-1: Preparing an Outline of Calendar by Using UserForm of VBA in Excel

In this step, we will prepare the outer template of the Calendar using UserForm. This is the primary step for Excel VBA Calendar.

First of all, go to the Developer tab >> select Visual Basic.
This will bring out the VBA Editor window.
Here, you can also press ALT + F11 keys to bring out the VBA Editor window.

At this moment, a VBA Editor window will appear.
After that, from the Insert tab >> select UserForm.

Therefore, you can see the UserForm.
Next, we will right-click on the UserForm >> select Properties.

Then, a Properties window will appear on the right side of the UserForm.
Furthermore, we will set the Name as Calendar.
Here, you can set any Name, however, since we are making a calendar, we put the name Calendar.
In addition, click on the drop-down arrow of the BackColor box.
Then, from Palette >> select White.

Also, set the Caption as Calendar.

Moreover, we click on the drop-down arrow of the SpecialEffect box.
We select 3-fmSpecialEffectEtched.

As a result, the outer template of the Excel VBA Calendar is ready.

Step-2: Inserting Labels into Our Created Outline of Calendar

In this step, we will insert labels for months and years into the Excel VBA calendar.

In the first place, click on the Toolbox.

Then, select a Label from the Toolbox >> draw the label in the Calendar.

Now, we have named the label Month.
Furthermore, we will right-click on Month >> select Properties.

After that, in the Properties window, click on the drop-down arrow of the BorderColor box.
Then, select 0-fmBackstyleTransparent.

In a similar way, we inserted the Year label.
Therefore, you can see two labels.

Step-3: Adding ComboBoxes

In this step, we will add two combo boxes for the Month and Year to Excel VBA Calendar.

First of all, we will select a ComboBox from the Toolbox.
Then, we will draw the ComboBox beside the Month.

Furthermore, we will right-click on the ComboBox >> select Properties.

Then, in the Properties window >> we will set the Name as Cmb_Month.

In addition, we will select 3-fmSpecialEfectEtched as the SpecialEffect.
And, we will select 2-fmStyleDropDownList as the Style.

Now, we will copy the ComboBox by right-clicking on it and selecting Copy.

After that, we placed the copied ComboBox bestie the Year label.
Furthermore, we will right-click on the ComboBox >> select Properties.

Then, a Properties window will appear.
Afterward, we set the Name as Cmb_Year.

At this moment, we will select all the labels and ComboBoxes by pressing the CTRL key.
After that, we will right-click on them.
Then, from the Align group >> we will select Middles.

As a result, you can see the Comboboxes for Month and Year.

Step-4: Writing Codes for ComboBoxes

In this step, we will write the codes for the ComboBoxes to show the Months and Years.

In the beginning, we will double-click anywhere on the Calendar.

Then, a Module to write code will appear.
Furthermore, we will type the following code.

Private Sub UserForm_Initialize()
Dim C_Month As Integer

For C_Month = 1 To 12
Me.Cmb_Month.AddItem VBA.Format(VBA.DateSerial(2020, C_Month, 1), "MMMM")
Next C_Month
Me.Cmb_Month.Value = VBA.Format(VBA.Date, "MMMM")

For C_Month = VBA.Year(Date) - 20 To VBA.Year(Date) + 20
Me.Cmb_Year.AddItem C_Month
Next C_Month
Me.Cmb_Year.Value = VBA.Format(VBA.Date, "YYYY")

End Sub

Later, Save the code.
Next, before running the code, we will return to the Calendar, and select the ComandBoxes by pressing the CTRL key.
Then, we will right-click >> select Properties.

After that, we will select 20 for ListRows.

This will display all the months at once and we do not need to scroll down to see all the months.

After that, we will Run the code.

Therefore, you can see the current month will appear.
You can click on the drop-down arrow and select any other month.

In addition, you can see the current Year.
You can click on the drop-down arrow and select any other year.
Hence, we have created the months and years in the Excel VBA calendar.

Step-5: Inserting Images to Calendar Outline

In this step, we will insert images into our Excel VBA calendar.

We will insert a background image, a right arrow, and a left arrow so that we can easily move toward the right and left.

First of all, we will select the Image from the ToolBox.
Then, we will draw the image box.

Next, we will right-click on the image box >> select Properties.

Then, we will select 0-fmBorderStyleNone as the BorderStyle.
Further, we will select a Picture.

Here, we have three pictures in a specific folder.
You can use any pictures you want.

Note: Your pictures must be in .jpg format. Also, you must resize your picture according to your needs, otherwise, the picture defaults to a big size which will be inconvenient to use in the Calendar.

Moreover, in the Picture box >> we will click on the three dots to insert an image.

Then, we selected the Back Ground image >> click Open.

Therefore, you can see the Picture in the Image box.

In addition, in the Properties box, we will select True as AutoSize.
This will make the picture size suitable.

In addition, we add a Right arrow and a Left arrow.
Next, we will click on the Left arrow.

This will open a Properties box.
Then, we will set the Name as Previous_Month.
In addition, we will set the ControlTipText as Previous_Month.

After that, we will click on the Left arrow.

This will open a Properties box.
Then, we will set the Name as Next_Month.
In addition, we will set the ControlTipText as Next_Month.

Step-6: Adding Month and Day Names in Calendar Outline

In this step, we will add the month and day names to the Excel VBA calendar.

First, to add the month name, we will copy the Month label.

Then, we will paste the Month into the calendar.
We will right-click on the Month >> select Properties.

Then, in the Properties window, we will set the Name as Month_Name.
Further, we will select a White Fore Color.
In addition, we will click on the Font box.

Then, a Font window will appear.
Afterward, we will select Bold as the Font Style >> select Size 9 >> click OK.

In addition, we will select Center text alignment.

Furthermore, we will place the Month in between the Left and Right arrows.
Next, we will add another image where we will put all the day names.
Therefore, from the Toolbox >> we select Image.
We place the Image like the following picture.

Then, we copy the Month label.
See the following picture.

Furthermore, we place the Month in the image box >> edit the name as Sun to indicate Sunday.
In addition, we right-click on the Sun >> select Properties.

Then, in the Properties box >> select Center as TextAlign.
In addition, click on the Font box.

Then, a Font window will appear.
Afterward, we will select Bold as the Font Style >> select Size 9 >> click OK.

Furthermore, we have added other days of the week.
Therefore, you can see all the day names.

Step-7: Adding CommandButtons

In this step, we will add Command Buttons for the dates in the Excel VBA Calendar.

In the beginning, from the Toolbox >> we will select CommandButton.
Then, we will draw the CommandButton under the days.

Furthermore, we will delete the name of the CommandButton.
Therefore, the CommandButton looks like the following picture.

In addition, we draw 41 Command Buttons.
Therefore, the total number of CommandButton is 42.
These commandbuttons will indicate the dates.

Note: You must insert the command button serially. Otherwise, the calendar will not run properly.

Step-8: Inserting Codes for CommandButtons

In this step, we will insert codes for CommandButtons.

In the beginning, we will double-click on the empty space in the Excel VBA Calendar.

Then, we will type the following code.

Private Sub D_Display()

 Dim D_Initial As Date
 D_Initial = VBA.DateValue("1-" & Me.Cmb_Month.Value _
 & "-" & Me.Cmb_Year.Value)

 Dim D_Final As Integer
 D_Final = VBA.Day(VBA.DateSerial(VBA.Year(D_Initial), _
 VBA.Month(D_Initial) + 1, 1) - 1)

 Dim C_Month As Integer
 Dim C_Date As CommandButton

 For C_Month = 1 To 42
  Set C_Date = Me.Controls("CommandButton" & C_Month)
  C_Date.Caption = ""
 Next C_Month

 For C_Month = 1 To 7
  Set C_Date = Me.Controls("CommandButton" & C_Month)
   If VBA.Weekday(D_Initial) = C_Month Then
 C_Date.Caption = "1"

  Else
    C_Date.Caption = ""
 End If

 Next C_Month

  Dim C_Date1 As CommandButton
  Dim C_Date2 As CommandButton

 For C_Month = 1 To 41
 Set C_Date1 = Me.Controls("CommandButton" & C_Month)
 Set C_Date2 = Me.Controls("CommandButton" & C_Month + 1)

 If C_Date1.Caption <> "" Then
 If D_Final > C_Date1.Caption Then
 C_Date2.Caption = C_Date1.Caption + 1

    End If
 End If
  Next C_Month
End Sub

Moreover, we will add Call D_Display in the previous code.
Then, we will Run the code.

Therefore, you can see the Calendar is looking much more effective.
You can also see the dates in the Command Buttons.

However, the dates are fixed for the month of January and the year 2023.
Next, to make the calendar dynamic, we will type the two codes along with the previous code.

Private Sub Cmb_Month_Change()
If Me.Cmb_Month.Value <> "" And Me.Cmb_Year.Value <> "" Then
  Call D_Display
  Me.Month_Name.Caption = Me.Cmb_Month & "-" & Me.Cmb_Year
 End If

End Sub

Private Sub Cmb_Year_Change()
If Me.Cmb_Month.Value <> "" And Me.Cmb_Year.Value <> "" Then
    Call D_Display
    Me.Month_Name.Caption = Me.Cmb_Month & "-" & Me.Cmb_Year
End If

End Sub

Next, we will again Run the code.
Now, we will change the month to April.

Therefore, you can see the calendar for April 2023.
Here, you can change the Year as well.

Step-9: Writing Codes for Left & Right Arrows

In this step, we will add codes to the Left and Right arrows to make the Excel VBA Calendar more dynamic and user-friendly.

In the beginning, we will double-click on the Right-arow.

This will open a Private Sub.

Then, we will type the following code.

Private Sub Next_Month_Click()
  On Error Resume Next
 If Me.Cmb_Month.ListIndex = 11 Then
    Me.Cmb_Month.ListIndex = 0
   Me.Cmb_Year.Value = Me.Cmb_Year.Value + 1
Else
   Me.Cmb_Month.ListIndex = Me.Cmb_Month.ListIndex + 1
 End If
End Sub

In addition, we will double-click on the Left arrow.

Moreover, we will type the following code.

Private Sub Previous_Month_Click()
  On Error Resume Next
  If Me.Cmb_Month.ListIndex = 0 Then
    Me.Cmb_Month.ListIndex = 11
  Me.Cmb_Year.Value = Me.Cmb_Year.Value - 1
 Else
  Me.Cmb_Month.ListIndex = Me.Cmb_Month.ListIndex - 1
  End If
End Sub

Now, Run the code.
At this point, click on the Left arrow.

Therefore, the previous month will be shown.

Along with that, click on the Right arrow.

As a result, the next month will be shown.

Step-10: Adding Color to Make Calendar Attractive

In this step, we will use code to add color to the Excel VBA Calendar. This will make the calendar more eye-catching.

First of all, we will add the following code at the end of the previous code.

Sub D_Col()

Dim C_Month As Integer
Dim C_Date As MSForms.CommandButton
For C_Month = 1 To 42
Set C_Date = Me.Controls("CommandButton" & C_Month)
C_Date.BackColor = VBA.RGB(217, 210, 233)
C_Date.Enabled = True
 Next C_Month
End Sub

In addition, we will have to add Call D_Col to the code that is under the D_Display sub.

Next, the final code for creating a calendar becomes

Private Sub Cmb_Month_Change()
If Me.Cmb_Month.Value <> "" And Me.Cmb_Year.Value <> "" Then
Call D_Display
Me.Month_Name.Caption = Me.Cmb_Month & "-" & Me.Cmb_Year
End If
End Sub
Private Sub Cmb_Year_Change()
If Me.Cmb_Month.Value <> "" And Me.Cmb_Year.Value <> "" Then
Call D_Display
Me.Month_Name.Caption = Me.Cmb_Month & "-" & Me.Cmb_Year
End If
End Sub

Private Sub Next_Month_Click()
On Error Resume Next
If Me.Cmb_Month.ListIndex = 11 Then
Me.Cmb_Month.ListIndex = 0
Me.Cmb_Year.Value = Me.Cmb_Year.Value + 1
Else
Me.Cmb_Month.ListIndex = Me.Cmb_Month.ListIndex + 1
End If
End Sub

Private Sub Previous_Month_Click()
On Error Resume Next
If Me.Cmb_Month.ListIndex = 0 Then
Me.Cmb_Month.ListIndex = 11
Me.Cmb_Year.Value = Me.Cmb_Year.Value - 1
Else
Me.Cmb_Month.ListIndex = Me.Cmb_Month.ListIndex - 1
End If
End Sub

Private Sub UserForm_Initialize()
Dim C_Month As Integer
For C_Month = 1 To 12
Me.Cmb_Month.AddItem VBA.Format(VBA.DateSerial(2020, C_Month, 1), "MMMM")
Next C_Month
Me.Cmb_Month.Value = VBA.Format(VBA.Date, "MMMM")
For C_Month = VBA.Year(Date) - 20 To VBA.Year(Date) + 20
Me.Cmb_Year.AddItem C_Month
Next C_Month
Me.Cmb_Year.Value = VBA.Format(VBA.Date, "YYYY")
Call D_Display
End Sub

Private Sub D_Display()
Dim D_Initial As Date
D_Initial = VBA.DateValue("1-" & Me.Cmb_Month.Value _
& "-" & Me.Cmb_Year.Value)
Dim D_Final As Integer
D_Final = VBA.Day(VBA.DateSerial(VBA.Year(D_Initial), _
VBA.Month(D_Initial) + 1, 1) - 1)
Dim C_Month As Integer
Dim C_Date As CommandButton
For C_Month = 1 To 42
Set C_Date = Me.Controls("CommandButton" & C_Month)
C_Date.Caption = ""
Next C_Month
For C_Month = 1 To 7
Set C_Date = Me.Controls("CommandButton" & C_Month)
If VBA.Weekday(D_Initial) = C_Month Then
C_Date.Caption = "1"
Else
C_Date.Caption = ""
End If
Next C_Month
Dim C_Date1 As CommandButton
Dim C_Date2 As CommandButton
For C_Month = 1 To 41
Set C_Date1 = Me.Controls("CommandButton" & C_Month)
Set C_Date2 = Me.Controls("CommandButton" & C_Month + 1)
If C_Date1.Caption <> "" Then
If D_Final > C_Date1.Caption Then
C_Date2.Caption = C_Date1.Caption + 1
End If
End If
Next C_Month
Call D_Col
End Sub

Sub D_Col()
Dim C_Month As Integer
Dim C_Date As MSForms.CommandButton
For C_Month = 1 To 42
Set C_Date = Me.Controls("CommandButton" & C_Month)
C_Date.BackColor = VBA.RGB(217, 210, 233)
C_Date.Enabled = True
Next C_Month
End Sub

At this point, Run the code.
Therefore, you can see the Calendar.

Practice Section

You can download the above Excel file and practice the explained steps.

Download Practice Workbook

You can download the Excel file and practice while reading this article.

Using VBA to Create Calendar.xlsm

Conclusion

Here, we show you 10 easy steps for Excel VBA Calendar. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.

Get FREE Advanced Excel Exercises with Solutions!

8 Comments

Derek Tierney Jun 29, 2023 at 6:38 PM

I got to the point where the dates are fixed in step 8
I added the two codes and when I selected run I got an error message
“Compile errpr:
Ambiguous name detected: Cmb_Month_Change”

Can you please advise?
Kind Regards
Derek Tierney
- Reply
  Afia Kona Jul 2, 2023 at 1:35 PM
  
  Dear DEREK TIERNEY,
  Thank you for your comment.
  After typing the code for the Command Button in Step 8, you have to call the private sub Call D_Display in the previous code. This will solve the problem. If you still face the issue, please attach the Excel file in the comment section.
  
  Best,
  Afia Aziz Kona
Matt Hamby Sep 14, 2023 at 12:04 AM

Very Nice! I used commandbuttons in lieu of inserting images, renamed them as “Previous_Month” and “Next_Month” respectfully. This works really nice! I love that I can mod all of the content to my liking. Now I have a question. I would like to add a few more items to this.
1. I would like to add a Text Box that will load with today’s date, I believe that should be simple enough through the initialize sub.
2. I would like to click on the commandbutton for a date and it will fill in that date into the text box mentioned above.
3. I would like for the commandbutton with the current date to be a different color than the rest. (I am changing the weekend colors so when the current date changes, these need to go back to the original color).
- Reply
  Aniruddah Alam Sep 19, 2023 at 3:29 PM
  Thanks a lot, MATT, for your comment. If you want to insert a Text Box that will display the current date, and highlight the current date, follow the steps below:
  First, draw a Textbox, rename it as “TextBox1”.
  You can also insert a Label to show what the Textbox will contain.
  Next, go to the D_Col subroutine and modify it in this way:
```
Sub D_Col()
Dim C_Month As Integer
Dim C_Date As MSForms.CommandButton
For C_Month = 1 To 42
    Set C_Date = Me.Controls("CommandButton" & C_Month)
    C_Date.BackColor = VBA.RGB(217, 210, 233)
    C_Date.Enabled = True
Next C_Month
'Initializing the Textbox with current date
Me.TextBox1.Value = Date
'Highlighting the current date
    For i = 1 To 41
        Dim cb As CommandButton
        Set cb = Me.Controls("CommandButton" & i)
        If cb.Caption = Day(Date) And Me.Cmb_Month.Value = VBA.Format(VBA.Date, "MMMM") And Me.Cmb_Year.Value = VBA.Format(VBA.Date, "YYYY") Then
        cb.BackColor = vbYellow 'Making it yellow
        End If
    Next i
End Sub
```
  As whenever any change happens in the userform, this D_Col subroutine is called, so it will always highlight the current date and populate the TextBox1 with the current date.
  
  If you want to populate the TextBox1 with the date of the clicked commandbutton, you need to write event driven subs CommandButton_Click for each of the CommandButtons from CommandButton1 to CommandButton42. Below, I am only giving the CommandButton1_Click sub.
```
Private Sub CommandButton1_Click()
 Dim btn As CommandButton
    Set btn = Me.ActiveControl
    Date_text = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text
    Me.TextBox1.Text = Date_text
End Sub
```
  You just need to replace the CommandButton1 with CommandButton2, CommandButton3 and so on for the rest of the CommandButtons.
  
  Now, if you run the UserForm, you will get your desired features.
  
  Here, I am attaching the Excel File with modified UserForm and VBA Codes :VBA UserForm Calender.xlsm
DanJ Oct 23, 2023 at 7:56 PM

Thank you so much! It is easy to use and connect to other forms and codes. It is easy to tailor to the specific needs for a calendar. I as the developer have total control over this calendar. I might have been able to develop one myself but it would have taken me a long time and it might not have been so user-friendly.
- Reply
  Shamima Sultana Oct 25, 2023 at 8:42 AM
  
  Hello DanJ,
  
  You are most welcome.
  
  Regards
  ExcelDemy

Rob Devine Mar 21, 2024 at 10:47 PM

This is fantastic.

Ive used the following to click a cell in my worksheet to open the calendar –

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Target.Count = 1) Then
If Not Intersect(Target, Range(“D75:D80”)) Is Nothing Then Calendar.Show
End If
End Sub

From Matt’s question above, how do I then add the SELECTED DATE into a cell I clicked to open the calendar in the excel sheet?
Thanks Rob.

Lutfor Rahman Shimanto Mar 24, 2024 at 1:56 PM

Hello Rob Devine

Thanks a lot for your kind words. Your appreciation means a lot to us.

You wanted to display the UserForm Calender when you select a single cell from the D75:D80 range and insert the date into the selected cell. Thanks once again for sharing such a practical requirement.

SOLUTION Overview:

To do so, follow these steps:

Double-click on the UserForm named Calender.

Paste the following code in the module and save it:

Private Sub CommandButton1_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton2_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton3_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton4_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton5_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton6_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton7_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton8_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton9_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton10_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton11_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton12_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton13_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton14_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton15_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton16_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton17_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton18_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton19_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton20_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton21_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton22_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton23_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton24_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton25_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton26_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton27_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton28_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton29_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton30_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton31_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton32_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton33_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton34_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton35_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton36_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton37_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub


Private Sub CommandButton38_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton39_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton40_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton41_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Private Sub CommandButton42_Click()
    
    Dim btn As CommandButton
    Set btn = Me.ActiveControl
    
    Selection.Value = btn.Caption & "-" & Cmb_Month.Text & "-" & Cmb_Year.Text

End Sub

Return to the sheet and make changes.

I have attached the solution workbook for better understanding. I hope the solution will fulfil your needs; good luck.

DOWNLOAD SOLUTION WORKBOOK

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

How to Create Calendar Using VBA in Excel (with Easy Steps)

Create a Calendar Using VBA in Excel: 10 Easy Steps

Step-1: Preparing an Outline of Calendar by Using UserForm of VBA in Excel

Step-2: Inserting Labels into Our Created Outline of Calendar

Step-3: Adding ComboBoxes

Step-4: Writing Codes for ComboBoxes

Step-5: Inserting Images to Calendar Outline

Step-6: Adding Month and Day Names in Calendar Outline

Step-7: Adding CommandButtons

Step-8: Inserting Codes for CommandButtons

Step-9: Writing Codes for Left & Right Arrows

Step-10: Adding Color to Make Calendar Attractive

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply

How to Create Calendar Using VBA in Excel (with Easy Steps)

Create a Calendar Using VBA in Excel: 10 Easy Steps

Step-1: Preparing an Outline of Calendar by Using UserForm of VBA in Excel

<img loading="lazy" decoding="async" class="aligncenter wp-image-320385 size-full" src="https://www.exceldemy.com/wp-content/uploads/2023/01/6.1-Excel-VBA-Calendar.png" alt="Created Outline for Excel VBA Calendar" width="477" height="432" />

Step-2: Inserting Labels into Our Created Outline of Calendar

Step-3: Adding ComboBoxes

Step-4: Writing Codes for ComboBoxes

Step-5: Inserting Images to Calendar Outline

Step-6: Adding Month and Day Names in Calendar Outline

Step-7: Adding CommandButtons

Step-8: Inserting Codes for CommandButtons

Step-9: Writing Codes for Left & Right Arrows

Step-10: Adding Color to Make Calendar Attractive

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply