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

Here’s the overview of the calendar we’re going to create.

Step 1 – Preparing an Outline of the Calendar with a UserForm and VBA in Excel

• Go to the Developer tab and select Visual Basic.
• This will bring out the VBA Editor window.
• You can also press Alt + F11 keys to bring out the VBA Editor window.

• From the Insert tab, select UserForm.

• You will see the UserForm.
• Right-click on the UserForm and select Properties.

• A Properties window will appear on the right side of the UserForm.
• Set the Name. You can set any Name, but we put the name Calendar.
• Click on the drop-down arrow of the BackColor box.
• From Palette, select White.

• Set the Caption as Calendar.
• Click on the drop-down arrow of the SpecialEffect box.
• Select 3-fmSpecialEffectEtched.

• The outer template of the Excel VBA Calendar is ready.

Step 2 – Inserting Labels into the Outline

•  Click on the Toolbox.

• Select a Label from the Toolbox and draw the label in the Calendar.

• We have named the label Month.
• Right-click on Month and select Properties.

• In the Properties window, click on the drop-down arrow of the BorderColor box.
• Select 0-fmBackstyleTransparent.

• We inserted the Year label in the same way.

Step 3 – Adding ComboBoxes

• Select a ComboBox from the Toolbox.
• Draw the ComboBox next to Month.

• Right-click on the ComboBox and select Properties.

• In the Properties window, set the Name as Cmb_Month.

• Select 3-fmSpecialEfectEtched as the SpecialEffect.
• Select 2-fmStyleDropDownList as the Style.

• Copy the ComboBox by right-clicking on it and selecting Copy.

• Paste the copied ComboBox next to the Year label.
• Right-click on the ComboBox and select Properties.

• A Properties window will appear.
• Set the Name as Cmb_Year.

• Select all the labels and ComboBoxes by pressing the Ctrl key.
• Right-click on them.
• From the Align group, we will select Middles.

• Here’s the result.

Step 4 – Inserting Code for the ComboBoxes

• Double-click anywhere on the Calendar.

• A Module to write code will appear.
• Insert 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

• Save the code.
• Return to the Calendar, and select the ComandBoxes while holding the Ctrl key.
• Right-click and select Properties.
• 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.

• Run the code.

• The current month will appear.
• You can click on the drop-down arrow and select any other month.

• You can click on the drop-down arrow for Year and select a value.

Step 5 – Inserting Images

• Select Image from the ToolBox.
• Draw the image box.

• Right-click on the image box and select Properties.

• Select 0-fmBorderStyleNone as the BorderStyle.
• Select a Picture.

• We have three pictures in a specific folder. You can use any pictures you want.

• In the Picture box, click on the three dots to insert an image.

• We selected the Back Ground image and clicked Open.

• You can see the Picture in the Image box.

• In the Properties box, select True for AutoSize.

• Add the Right arrow and Left arrow pictures.
• Click on the Left arrow.

• This will open a Properties box.
• Set the Name as Previous_Month.
• Set the ControlTipText as Previous_Month.

• Click on the Right arrow.

• This will open a Properties box.
• Set the Name as Next_Month.
• Set the ControlTipText as Next_Month.

Step 6 – Adding Month and Day Names

• Copy the Month label.

• Paste the Month into the calendar.
• Right-click on the Month and select Properties.

• Set the Name as Month_Name.
• Choose a White ForeColor.
• Click on the Font box.

• Select Bold as the Font Style, choose Size 9, and click OK.

• Select Center for TextAlign.

• Place the Month between the Left and Right arrow.
• From the Toolbox, select an Image. We placed the Image like the following picture.

• Copy the Month label.

• Paste the Month in the image box and edit the name as Sun to indicate Sunday.
• Right-click on Sun and select Properties.

• Select Center for TextAlign.
• Click on the Font box.

• Select Bold as the Font Style, choose Size 9, and click OK.

• We have added other days of the week by repeating the process.

Step 7 – Adding CommandButtons

• From the Toolbox, select CommandButton.
• Draw the CommandButton under the days.

• Delete the name of the CommandButton.

• Draw 41 more Command Buttons.
• These commandbuttons will indicate the dates.

Step 8 – Inserting Codes for CommandButtons

• Double-click on the empty space in the Excel VBA Calendar.

• Insert 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

• Run the code.

• The Calendar is looking better. You can also see the dates in the Command Buttons.

• The dates are fixed for the month of January and the year 2023.
• To make the calendar dynamic, insert the following code in the UserBox module.

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

• Run the code.
• Change the month to April.

• You can see the calendar for April 2023. You can also change the Year.

Step 9 – Using Code for the Left and Right Arrows

• Double-click on the Right arrow.

• Insert 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

• Double-click on the Left arrow.

• Insert 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

• Run the code.
• Click on the Left arrow.

• The previous month will be shown.

• Click on the Right arrow.

• The next month will be shown.

Step 10 – Adding Colors

• 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

• Add Call D_Col to the code that is under the D_Display sub.

• Here’s the resulting 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

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

• Run the code.

Practice Section

You can download the above Excel file and use the template to test it out.

Download the Template

Related Articles

• Excel VBA Events
• How to Use VBA Event Handler
• How to Use VBA OnKey Event in Excel
• Excel VBA Open Workbook Event
• Excel VBA: Workbook Level Events and Their Uses