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.

Download Practice Workbook

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

10 Easy Steps to Create a Calendar Using VBA in Excel

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 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: Insert 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 Combo Box

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 place 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 Code 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 Image 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.

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