To add a calendar to an Excel VBA UserForm, create a small date-picker form with standard Microsoft Forms buttons and labels, then open it from your main form and return the selected date. This approach works in Excel for Microsoft 365 on Windows without relying on the older MonthView control, which is missing from many current installations. You will need the VBA editor and a workbook saved as a macro-enabled file.
What You’ll Need
- Excel for Microsoft 365 on Windows with macros enabled.
- A workbook saved as an Excel Macro-Enabled Workbook (
.xlsm). - A main UserForm containing a date TextBox and a button that opens the calendar. If you do not have a main form yet, you can create one as part of this guide.
- Two UserForms and one class module in the VBA editor. The calendar’s buttons are created in code, so you do not need to place 42 day buttons manually.
Step-by-Step
- Save a copy and open the VBA editor. Save a backup copy before adding or running VBA. Macros cannot be undone with Ctrl+Z, and code changes can affect the workbook. In Excel for Windows, press Alt+F11 to open the Visual Basic for Applications editor. On a Mac, use Option+F11; depending on your keyboard settings, you may also need Fn. The VBA editor and UserForms are not supported in Excel for the web, so use the desktop application.
- Create the calendar UserForm. In the VBA editor, choose Insert > UserForm. In the Properties window, change the form’s (Name) property to
frmCalendar. If the Properties window is hidden, press F4. The form will create its own month heading, navigation buttons, weekday labels, and day buttons when it opens. You do not need to draw those controls in the designer. - Add a class module to handle button clicks. Choose Insert > Class Module. In the Properties window, set the class module’s (Name) property to
cCalendarButton. This class listens for clicks on each button created at runtime and passes the button’s tag to the calendar form. Keeping the event code in a class is what lets one shared handler serve all the date buttons. - Paste the event-handler code into the class module. Select the class module and paste this code. The tag will contain either a navigation instruction or the selected date in year-month-day form.
Option Explicit
Public WithEvents Button As MSForms.CommandButton
Private mOwner As frmCalendar
' Connect a runtime button to its calendar form.
Public Sub Connect(ByVal ctl As MSForms.CommandButton, _
ByVal owner As frmCalendar)
Set Button = ctl
Set mOwner = owner
End Sub
Private Sub Button_Click()
mOwner.HandleCalendarClick CStr(Button.Tag)
End Sub
- Build the calendar when the form opens. Open
frmCalendarin the code window and paste the following form code. The form creates its controls, retains event-handler objects in a collection, draws the current month, and handles both navigation and date selection. Its first displayed month is the current month.
Option Explicit
Private mDisplayedMonth As Date
Private mHandlers As Collection
Public SelectedDate As Date
Public WasCancelled As Boolean
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim btn As MSForms.CommandButton
Dim lbl As MSForms.Label
Dim handler As cCalendarButton
Dim i As Long
Dim col As Long
Dim row As Long
Dim dayNames As Variant
Me.Caption = "Choose a date"
Me.Width = 250
Me.Height = 280
WasCancelled = True
SelectedDate = Date
mDisplayedMonth = DateSerial(Year(Date), Month(Date), 1)
Set mHandlers = New Collection
Set btn = Me.Controls.Add("Forms.CommandButton.1", _
"cmdPrev", True)
btn.Caption = "<"
btn.Left = 12
btn.Top = 12
btn.Width = 30
btn.Height = 22
btn.Tag = "PREV"
Set handler = New cCalendarButton
handler.Connect btn, Me
mHandlers.Add handler
Set lbl = Me.Controls.Add("Forms.Label.1", "lblMonth", True)
lbl.Left = 48
lbl.Top = 16
lbl.Width = 145
lbl.Height = 18
lbl.TextAlign = fmTextAlignCenter
Set btn = Me.Controls.Add("Forms.CommandButton.1", _
"cmdNext", True)
btn.Caption = ">"
btn.Left = 199
btn.Top = 12
btn.Width = 30
btn.Height = 22
btn.Tag = "NEXT"
Set handler = New cCalendarButton
handler.Connect btn, Me
mHandlers.Add handler
dayNames = Array("Sun", "Mon", "Tue", "Wed", _
"Thu", "Fri", "Sat")
For col = 0 To 6
Set lbl = Me.Controls.Add("Forms.Label.1", _
"lblWeek" & CStr(col), True)
lbl.Caption = dayNames(col)
lbl.Left = 12 + col * 31
lbl.Top = 43
lbl.Width = 28
lbl.Height = 18
lbl.TextAlign = fmTextAlignCenter
Next col
For i = 1 To 42
col = (i - 1) Mod 7
row = (i - 1) \ 7
Set btn = Me.Controls.Add("Forms.CommandButton.1", _
"day" & Format$(i, "00"), True)
btn.Left = 12 + col * 31
btn.Top = 63 + row * 26
btn.Width = 28
btn.Height = 22
btn.Caption = ""
btn.Tag = ""
btn.Enabled = False
Set handler = New cCalendarButton
handler.Connect btn, Me
mHandlers.Add handler
Next i
DrawMonth
End Sub
Private Sub DrawMonth()
Dim firstSlot As Long
Dim daysInMonth As Long
Dim i As Long
Dim dayNumber As Long
Dim btn As MSForms.CommandButton
Dim dateValue As Date
Me.Controls("lblMonth").Caption = _
Format$(mDisplayedMonth, "mmmm yyyy")
firstSlot = Weekday(mDisplayedMonth, vbSunday)
daysInMonth = Day(DateSerial(Year(mDisplayedMonth), _
Month(mDisplayedMonth) + 1, 0))
For i = 1 To 42
Set btn = Me.Controls("day" & Format$(i, "00"))
dayNumber = i - firstSlot + 1
If dayNumber >= 1 And dayNumber <= daysInMonth Then
dateValue = DateSerial(Year(mDisplayedMonth), _
Month(mDisplayedMonth), dayNumber)
btn.Caption = CStr(dayNumber)
btn.Tag = Format$(dateValue, "yyyy-mm-dd")
btn.Enabled = True
Else
btn.Caption = ""
btn.Tag = ""
btn.Enabled = False
End If
Next i
End Sub
Public Sub HandleCalendarClick(ByVal key As String)
Dim parts As Variant
Select Case key
Case "PREV"
mDisplayedMonth = DateAdd("m", -1, mDisplayedMonth)
DrawMonth
Case "NEXT"
mDisplayedMonth = DateAdd("m", 1, mDisplayedMonth)
DrawMonth
Case Else
parts = Split(key, "-")
If UBound(parts) = 2 Then
SelectedDate = DateSerial(CInt(parts(0)), _
CInt(parts(1)), _
CInt(parts(2)))
WasCancelled = False
Me.Hide
End If
End Select
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
' Keep the form loaded long enough for the caller to read its result.
If CloseMode = vbFormControlMenu Then
Cancel = True
WasCancelled = True
Me.Hide
End If
End Sub
- Create or prepare the main entry form. Choose Insert > UserForm and name this form
frmEntry. In the designer, add a TextBox namedtxtDateand a CommandButton namedcmdChooseDate. Set the button’s Caption to something clear, such as “Choose date”. The names matter because the event code below refers to them directly. If you already have an entry form, use your existing control names in the code instead. - Open the calendar and return its selection. Double-click the button on
frmEntryand paste this event procedure. It shows the picker modally, so the user must select a date or close the picker before returning to the entry form.
Option Explicit
Private Sub cmdChooseDate_Click()
Dim picker As frmCalendar
Set picker = New frmCalendar
picker.Show vbModal
If Not picker.WasCancelled Then
' Assign the date value, not a formatted text string.
Me.txtDate.Value = picker.SelectedDate
End If
Unload picker
Set picker = Nothing
End Sub
- Set an appropriate display format and test. A UserForm TextBox does not have a worksheet-style NumberFormat property. Assigning the selected date returns a date value converted to text for display according to the computer’s regional settings. If you need a fixed display such as day-month-year, use
Format$(picker.SelectedDate, "dd-mmm-yyyy")when assigning the value; note that this puts formatted text in the TextBox rather than a date value. Test the previous and next month buttons, the first and last days of a month, and the close button. If you need to use the entry later as a date, validate or convert the TextBox value when saving it.
Common Mistakes
Using the legacy MonthView or Date and Time Picker control. Older tutorials often ask you to add a MonthView ActiveX control through Additional Controls in the Toolbox. That control may not be installed, may be unavailable in 64-bit Office, or may be blocked by your organization’s settings. The standard Forms controls created above avoid that dependency. If you choose to use a legacy control anyway, confirm that it is installed and compatible with the specific Office installation before designing around it.
Forgetting the class-module name or form name. VBA needs the class to be named exactly cCalendarButton and the picker UserForm to be named frmCalendar for the declarations and object references shown here to compile. Check the (Name) field in the Properties window; changing a form’s Caption does not change its VBA name.
Not retaining the event objects. The mHandlers collection is essential. It keeps each button-handler instance alive while the form is open. If you remove the collection or fail to add each handler to it, the runtime buttons may appear but their Click events will not be handled consistently.
Mixing up date strings and date values. The picker passes its day as an ISO-style year-month-day tag and reconstructs it with DateSerial. This avoids relying on the computer’s interpretation of a slash-separated date. The TextBox display is a separate matter: setting its Value to a Date does not give it a fixed visual format, and saving text from a TextBox may require validation.
Expecting Ctrl+Z to reverse a macro or code edit. Excel does not provide an undo stack for VBA macros. Save a separate workbook copy before running code that changes data, and keep a copy of the original code before making substantial edits.
Tips
For a Monday-first calendar, make two coordinated edits: change the weekday array to start with Monday, and change vbSunday in the Weekday call to vbMonday. Changing only one will put dates beneath the wrong weekday headings. You can also add a Clear button or a Today button, but give each new button its own tag and handle that tag in HandleCalendarClick.
If the calendar should initially open to a date already entered in txtDate, add a public date property to the calendar form and set it before calling Show. In UserForm_Initialize, use that date when it is valid instead of always using Date. Keep the month value set to the first day of that month with DateSerial, while retaining the original date as the selected value if you want to highlight it. This is a useful enhancement, but the basic version intentionally opens on the current month to keep the setup straightforward.
To make the picker easier to use, give the forms descriptive captions, use a readable font size, and check how the buttons look at your display scaling. The controls are positioned in points, so their spacing may look different on a high-DPI display. If you change the form width, adjust the day-button spacing and weekday-label positions together. Avoid naming new controls with names that conflict with built-in properties or the existing controls.
The example starts weeks on Sunday and permits navigation to any month supported by VBA dates. If your workbook has a narrower allowed date range, add a check before the previous or next month action and disable navigation outside that range. For a business-only date picker, explain whether weekends can be selected and enforce that rule in the shared selection handler. Visual styling alone is not validation: a user may still enter a date directly into the TextBox.
Troubleshooting
Compile error: User-defined type not defined. Check that the class module exists and is named cCalendarButton, and that the calendar form’s name is frmCalendar. Also confirm that the Microsoft Forms library is available in the VBA project. A normal Excel UserForm project generally has the required Forms types; do not replace the declared control types with unrelated Excel worksheet controls.
The calendar opens, but clicking a day does nothing. Confirm that mHandlers is declared at the top of the form and initialized before controls are created. Confirm that every day button’s handler is added to the collection. Also check that the day button is enabled and that its Tag is populated; blank cells in the calendar grid are intentionally disabled.
Controls do not fit or are cut off. Verify that the form width and height are large enough for the positions in the code. The last day-button row begins at 193 points and is 22 points high, so the form needs room below that for its border. Increase Me.Height if the bottom row is clipped. If you alter the spacing, update the weekday labels and day buttons consistently.
The date appears in a different order than expected. The picker returns a Date, but the TextBox displays a string using Windows regional settings. To show a consistent label, assign a formatted string such as day, abbreviated month, and year. If you need to store or compare the date, retain a Date value in a worksheet cell or variable rather than relying on display text alone.
It fails in Excel for the web or on a Mac. Excel for the web cannot run VBA UserForms. The code and control setup here target Excel for Microsoft 365 on Windows. Excel for Mac includes VBA support, but ActiveX controls and some form-control behavior differ; do not assume Windows-specific controls will transfer unchanged. Test the workbook in the exact Mac version you need to support, or use a platform-independent input method such as a validated date field.
Frequently Asked Questions
Do I need to install a calendar add-in?
No. This guide uses standard Microsoft Forms controls created by VBA. It does not depend on a separate add-in or the legacy MonthView ActiveX control.
Can I put the calendar directly on a worksheet?
Yes, but that is a different design. This example creates a pop-up UserForm. A worksheet calendar needs its own cell layout and selection logic, or a compatible third-party control.
Can the calendar return the selected date to a worksheet cell?
Yes. After the picker closes, write picker.SelectedDate to a target cell before unloading the picker. Use a real date value and format the cell with Excel’s Number Format controls so the display does not determine the stored date.
Why not use a Date Picker control from the Toolbox?
Some older Office installations include a date-related ActiveX control, but it is not consistently available across current Windows installations and Office bitness. A standard-control calendar is a more portable VBA option for a Windows UserForm.
Can users type a date instead of choosing one?
Yes. A TextBox allows direct typing. Validate its contents when the user saves the form, because the calendar selection does not prevent manually entered invalid or out-of-range text.
Can I change the first day of the week?
Yes. Update both the weekday-name array and the first-day argument to Weekday as described in Tips. They must agree so each date lines up under the correct heading.
Related
- Group Rows in Excel by Column Value: 3 Methods
- Goal Seek in Excel: Find the Input for a Target
- Gold Data in Excel: Build a PivotTable
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Case Statement: How Select Case WorksExcel VBA does not have a separate Case statement; it uses Select Case ... End Select to choose…
- VBA Copy Paste Row: Excel Code and Common FixesTo copy an entire row to another row in Excel with VBA, use Rows(sourceRow).Copy Destination:=Rows(targetRow). For example, Worksheets("Data").Rows(5).Copy…
- VBA Open UserForm on Button Click: Code and Setup GuideTo open an Excel VBA UserForm when a worksheet button is clicked, create a public macro in a…
- Excel VBA AutoFilter: Show All DataTo show every row hidden by an Excel AutoFilter, use VBA’s ShowAllData method, but first check that the…
- VBA Rename UserForm: Steps, Code, and Common IssuesTo rename a VBA UserForm in Excel for Microsoft 365 on Windows, open the Visual Basic Editor, select…
- Excel Macro to Move Down One Cell in the Active SheetTo move the active cell down one row in Excel, run a VBA macro that selects ActiveCell.Offset(1, 0).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.