VBA Calendar UserForm: Build a Date Picker in Excel

VBA & macros · Updated November 18, 2026

A VBA calendar UserForm is a custom date picker that lets someone choose a date inside an Excel form instead of typing it into a cell. In Excel for Microsoft 365 on Windows, the dependable approach is to build the calendar from ordinary UserForm controls; older ActiveX calendar controls are often missing or incompatible.

What a VBA calendar UserForm does

A UserForm is a small window created with Excel’s Visual Basic for Applications editor. A calendar UserForm typically shows a month, lets the user move between months, and returns the selected date to a worksheet cell, a text box, or another part of a VBA form. It is useful when people enter dates repeatedly and need a clear way to avoid ambiguous typing such as 4/5/26, which can mean different dates under different regional settings.

Excel does not provide a built-in calendar UserForm that can simply be switched on for every workbook. You either create one from standard form controls, use a calendar control that happens to be installed on a particular computer, or choose a non-VBA input method. The best choice depends on who will use the workbook, how it will be distributed, and whether macros are permitted.

Why older calendar controls cause trouble

Many older tutorials use the Microsoft Date and Time Picker or MonthView ActiveX control. Those controls relied on the MSCOMCT2.OCX component and are not a dependable default in current Office installations. They may be absent from the Insert Controls list, unavailable in 64-bit Office, or not registered on another person’s computer. A workbook that works on its author’s computer can therefore fail when opened elsewhere.

ActiveX controls also add deployment and security considerations: users may need the same component installed and properly registered, and organizational policies may block ActiveX. They are not a practical cross-platform solution for Mac or Excel for the web. A custom calendar assembled from standard UserForm buttons and labels avoids that particular dependency, although VBA UserForms themselves still require desktop Excel with macros enabled.

Choose an approach before building

For a workbook used by a small, controlled group on Windows, a custom UserForm is reasonable when a visual calendar materially improves data entry. For a shared workbook, a simple date-entry field with validation may be easier to maintain. For a process used in Excel for the web, VBA is not an option because Excel for the web cannot run VBA macros. Consider the audience before investing in a form that some users cannot open.

  • Custom VBA calendar: Works without the legacy calendar OCX, but needs a macro-enabled workbook and a little setup.
  • Date validation: Keeps entry in worksheet cells and can restrict values to dates or a permitted range, without creating a popup calendar.
  • Legacy ActiveX calendar: May suit a controlled older Windows environment, but has significant installation and compatibility risks.
  • Non-VBA workflow: Use an approved form, application, or platform-specific date picker if web or Mac access is essential.

Plan the custom calendar layout

A practical month view needs a month heading, previous- and next-month buttons, weekday headings, and a six-row-by-seven-column grid of day buttons. Six rows are needed because some months span parts of six calendar weeks. For the example below, create a UserForm named frmCalendar. Add a label named lblMonth, buttons named cmdPrev, cmdNext, and cmdCancel, plus 42 CommandButtons named day1 through day42.

Arrange the 42 day buttons in seven columns and six rows beneath weekday labels ordered Monday through Sunday. The code calculates dates using a Monday-first week. Set each day button’s caption to a small readable size and make the month heading prominent. The weekday labels are decorative; they do not need special VBA names. A consistent grid, a clear Cancel button, and a visible month and year make the form easier to use than a bare cluster of buttons.

Connect day buttons and draw the month

VBA does not automatically give a single event procedure to a changing collection of buttons. A class module with a WithEvents variable can listen for clicks on each day button. In the VBA editor, insert a Class Module, rename it CCalendarButton, and add the first code block below. Then put the second block in the code module for frmCalendar. The form code clears all 42 buttons, calculates the first weekday and the number of days, and enables only the buttons that represent dates in the displayed month.

Option Explicit

Private WithEvents mButton As MSForms.CommandButton
Private mParent As frmCalendar

Public Sub Connect(ByVal dayButton As MSForms.CommandButton, _
                   ByVal parentForm As frmCalendar)
    Set mButton = dayButton
    Set mParent = parentForm
End Sub

Private Sub mButton_Click()
    mParent.ChooseDay mButton
End Sub
Option Explicit

Private mHandlers As Collection
Private mMonth As Date
Public PickedDate As Date
Public WasCancelled As Boolean

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim handler As CCalendarButton

    Set mHandlers = New Collection
    For i = 1 To 42
        Set handler = New CCalendarButton
        handler.Connect Me, Me.Controls("day" & CStr(i))
        mHandlers.Add handler
    Next i

    mMonth = DateSerial(Year(Date), Month(Date), 1)
    WasCancelled = True
    DrawCalendar
End Sub

Private Sub DrawCalendar()
    Dim firstDay As Date
    Dim offset As Long
    Dim daysInMonth As Long
    Dim i As Long
    Dim dayNumber As Long
    Dim dayButton As MSForms.CommandButton

    firstDay = DateSerial(Year(mMonth), Month(mMonth), 1)
    offset = Weekday(firstDay, vbMonday) - 1
    daysInMonth = Day(DateSerial(Year(mMonth), Month(mMonth) + 1, 0))
    lblMonth.Caption = Format$(mMonth, "mmmm yyyy")

    For i = 1 To 42
        Set dayButton = Me.Controls("day" & CStr(i))
        dayButton.Caption = ""
        dayButton.Enabled = False
    Next i

    For dayNumber = 1 To daysInMonth
        Set dayButton = Me.Controls("day" & CStr(offset + dayNumber))
        dayButton.Caption = CStr(dayNumber)
        dayButton.Enabled = True
    Next dayNumber
End Sub

Public Sub ChooseDay(ByVal dayButton As MSForms.CommandButton)
    If Len(dayButton.Caption) = 0 Then Exit Sub
    PickedDate = DateSerial(Year(mMonth), Month(mMonth), _
                            CLng(dayButton.Caption))
    WasCancelled = False
    Me.Hide
End Sub

Private Sub cmdPrev_Click()
    mMonth = DateAdd("m", -1, mMonth)
    DrawCalendar
End Sub

Private Sub cmdNext_Click()
    mMonth = DateAdd("m", 1, mMonth)
    DrawCalendar
End Sub

Private Sub cmdCancel_Click()
    WasCancelled = True
    Me.Hide
End Sub

The collection named mHandlers is important: it keeps the event-listener objects alive for as long as the form is open. Without a persistent reference, the listeners can be discarded and clicking a day may do nothing. The grid uses Monday as its first weekday. To use Sunday-first weeks, change the weekday calculation and arrange the headings and buttons to match; changing only the labels would make the dates appear under the wrong weekday.

Return the chosen date to the worksheet

Put this small procedure in a standard module, such as Module1. It displays the form modally, which means Excel waits for the user to choose a date or cancel before continuing. This example writes the result to the active cell; adapt that one line if the date belongs in a known cell or a form text box.

Option Explicit

Public Sub ChooseDateForActiveCell()
    Dim calendarForm As frmCalendar

    Set calendarForm = New frmCalendar
    calendarForm.Show vbModal

    If Not calendarForm.WasCancelled Then
        ActiveCell.Value = calendarForm.PickedDate
        ActiveCell.NumberFormat = "yyyy-mm-dd"
    End If

    Unload calendarForm
    Set calendarForm = Nothing
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA. On Windows, Alt+F11 opens the VBA editor; on many Mac keyboards, Option+F11 opens it, although an Fn key may also be required depending on the keyboard. UserForms and VBA are desktop features, not a way to make a calendar run in Excel for the web.

Validation, usability, and regional settings

The example constructs the selected value with DateSerial, so it returns a genuine Excel date value rather than text that merely looks like a date. The number format controls its display, not the underlying date. The example uses the unambiguous display format yyyy-mm-dd, but a workbook can use another format suited to its users. Excel may display dates differently under regional settings, so storing a real date is safer than assembling a text string such as “03/04/2026.”

For production use, decide whether to limit the allowed date range. For example, a booking form might prevent dates in the past, while a historical data form may need to accept them. Add that rule inside ChooseDay before setting WasCancelled to False, and show a clear message when the selection is rejected. Also consider keyboard navigation and accessibility: buttons should have useful captions, the form should have a meaningful title, and users should not be forced to rely on color alone to understand the selected date.

Common problems and how to diagnose them

  • Compile error on MSForms.CommandButton: Confirm the code is in a workbook using Excel’s UserForms, that the control is a CommandButton, and that the class name is exactly CCalendarButton. Use Debug > Compile VBAProject to locate compile errors.
  • Object not found during initialization: Check every control name against the instructions, including all 42 day buttons. A misspelling such as days1 instead of day1 causes a runtime error.
  • Buttons show no dates: Ensure the controls are on frmCalendar itself, the form initializes successfully, and the click handlers are retained in the collection.
  • Dates appear under the wrong weekday: The example is Monday-first. The weekday labels and physical button order must match its vbMonday calculation.
  • The macro is blocked: The workbook must be in a macro-capable format, and Excel’s security settings or organizational policy must permit the code. Do not advise users to enable all macros globally; follow their organization’s trusted-file process.

When another solution is better

A calendar popup is not always worth the maintenance. If users only enter occasional dates, a cell with date validation and a clear number format may be sufficient; validation can reject invalid entries, though it does not create the same calendar window. If the workbook is shared through a browser, choose a web-compatible data-entry approach because browser Excel does not execute VBA. If Mac users are in scope, test the form on the actual Excel for Mac versions used by the group; ActiveX is Windows-specific, and differences in available controls or VBA behavior make Windows-only assumptions risky.

For a larger project, separate the calendar’s display logic from the workbook’s data-writing logic. The sample does this in a basic way: the form returns a date and the caller decides where to write it. That makes it easier to reuse the picker from another form or target cell. It also makes testing clearer: verify month transitions, leap years, month boundaries, cancellation, and the date value returned before distributing the workbook.

Frequently Asked Questions

Does Excel have a built-in VBA calendar UserForm?
Not a universally available built-in calendar form. You can create one from standard UserForm controls, or use a legacy control if it is installed, but the latter is not reliably portable.

Why can’t I find the Date and Time Picker control?
It is a legacy ActiveX component that may not be installed or compatible with the Office setup, especially in current 64-bit installations. A custom button-based form avoids relying on that component.

Will a VBA calendar UserForm work in Excel for the web?
No. Excel for the web can open many macro-enabled workbooks, but it cannot run VBA macros or display a VBA UserForm. Use a web-compatible workflow for browser users.

Can the calendar start on Sunday instead of Monday?
Yes. Change the weekday offset calculation to use vbSunday and arrange the weekday labels and day-button grid Sunday-first. Both the calculation and layout need to agree.

Can I use this form on a Mac?
VBA is available in desktop Excel for Mac, but control availability and behavior can differ, and ActiveX controls are not a Mac solution. Test a custom UserForm on the target Mac version before relying on it.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.