Excel VBA Date Picker UserForm: Build One in Excel

VBA & macros · Updated November 21, 2026

To make an Excel VBA date picker UserForm, create a modal UserForm with month-navigation buttons and a calendar grid, then write the selected date to the active cell. The Windows example below builds the grid in VBA, works without a third-party calendar control, and includes setup instructions and common compatibility cautions.

What a VBA date picker does

A date picker is a small dialog that lets someone choose a date instead of typing it into a worksheet cell. A VBA UserForm can present a month at a time, let the user move between months, and return a selected date to the macro that opened the form. Because it runs as part of the workbook, you can control when it appears and which cell receives the result.

Excel does not provide a built-in, universal popup calendar for worksheet cells in Microsoft 365 for Windows. Data validation can restrict entries to dates, but it does not provide a calendar interface. A UserForm is one way to add that interface; it is separate from Excel’s built-in data validation and formatting features.

The example here creates its day buttons at runtime and uses a small class module to respond to their clicks. It does not depend on the old Microsoft Date and Time Picker control, which is unavailable in many current Office installations and is especially problematic across 32-bit and 64-bit setups. The code is intended for desktop Excel on Windows, not Excel for the web.

Prepare the UserForm and controls

First save a copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). Press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > UserForm, then set the form’s (Name) property to frmDatePicker. Add these controls from the Toolbox and set their names and captions:

  • A Label named lblMonth, with a blank caption.
  • A CommandButton named cmdPrevious, captioned <.
  • A CommandButton named cmdNext, captioned >.
  • A CommandButton named cmdToday, captioned Today.
  • A CommandButton named cmdCancel, captioned Cancel.

Arrange the month label between the previous and next buttons. Leave room below it for seven weekday headings and six rows of day buttons; the code creates those calendar controls automatically. Put Today and Cancel below the grid. The exact dimensions are adjustable, but the form should be wide enough for seven columns and tall enough for six rows. For accessibility, use readable text and avoid relying on color alone to show which date is selected.

In the Project Explorer, choose Insert > Class Module and set its (Name) property to cDayButton. Add the following code to that class module:

Option Explicit

Public WithEvents DayButton As MSForms.CommandButton
Public Owner As frmDatePicker

Private Sub DayButton_Click()
    ' Send a valid day click back to the form.
    If Len(DayButton.Tag) > 0 Then Owner.SelectDay CLng(DayButton.Tag)
End Sub

The class holds a reference to each generated button and receives its Click event. A normal array of buttons is not enough for this pattern: each button needs a WithEvents object, and the form must keep those objects alive in a collection while it is open.

Build the calendar grid

Double-click the UserForm and paste this code into its code module. The seven weekday headings start on Monday. Each month is displayed as a six-row grid, so its layout remains consistent even when a month starts late in the week.

Option Explicit

Private mDisplayedMonth As Date
Private mDayHooks As Collection
Public SelectedDate As Date
Public WasCancelled As Boolean

Private Sub UserForm_Initialize()
    Set mDayHooks = New Collection
    mDisplayedMonth = DateSerial(Year(Date), Month(Date), 1)
    WasCancelled = True
    MakeCalendarGrid
    PaintMonth
End Sub

Private Sub MakeCalendarGrid()
    Dim names As Variant
    Dim i As Long
    Dim heading As MSForms.Label
    Dim dayButton As MSForms.CommandButton
    Dim hook As cDayButton

    names = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

    For i = 0 To 6
        Set heading = Me.Controls.Add("Forms.Label.1", "weekday" & CStr(i + 1), True)
        heading.Caption = names(i)
        heading.Left = 12 + i * 31
        heading.Top = 54
        heading.Width = 29
        heading.Height = 18
        heading.TextAlign = fmTextAlignCenter
    Next i

    For i = 1 To 42
        Set dayButton = Me.Controls.Add("Forms.CommandButton.1", "day" & CStr(i), True)
        dayButton.Left = 12 + ((i - 1) Mod 7) * 31
        dayButton.Top = 74 + ((i - 1) \ 7) * 25
        dayButton.Width = 29
        dayButton.Height = 22
        dayButton.Caption = ""
        dayButton.Tag = ""
        dayButton.Enabled = False

        Set hook = New cDayButton
        Set hook.DayButton = dayButton
        Set hook.Owner = Me
        mDayHooks.Add hook
    Next i
End Sub

Private Sub PaintMonth()
    Dim i As Long
    Dim dayNumber As Long
    Dim firstSlot As Long
    Dim daysInMonth As Long
    Dim hook As cDayButton

    lblMonth.Caption = Format$(mDisplayedMonth, "mmmm yyyy")
    firstSlot = Weekday(mDisplayedMonth, vbMonday)
    daysInMonth = Day(DateSerial(Year(mDisplayedMonth), Month(mDisplayedMonth) + 1, 0))

    For i = 1 To 42
        dayNumber = i - firstSlot + 1
        Set hook = mDayHooks(i)
        With hook.DayButton
            If dayNumber >= 1 And dayNumber <= daysInMonth Then
                .Caption = CStr(dayNumber)
                .Tag = CStr(dayNumber)
                .Enabled = True
            Else
                .Caption = ""
                .Tag = ""
                .Enabled = False
            End If
        End With
    Next i
End Sub

Private Sub cmdPrevious_Click()
    mDisplayedMonth = DateAdd("m", -1, mDisplayedMonth)
    PaintMonth
End Sub

Private Sub cmdNext_Click()
    mDisplayedMonth = DateAdd("m", 1, mDisplayedMonth)
    PaintMonth
End Sub

Private Sub cmdToday_Click()
    SelectedDate = Date
    WasCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    Me.Hide
End Sub

Public Sub SelectDay(ByVal dayNumber As Long)
    SelectedDate = DateSerial(Year(mDisplayedMonth), Month(mDisplayedMonth), dayNumber)
    WasCancelled = False
    Me.Hide
End Sub

In the form designer, make sure the named controls match the code exactly; VBA event procedures do not run if a button is accidentally named something else. Position the weekday headings at the top of the grid, matching the code’s left and top offsets. Leave space for the sixth row and the footer buttons. This is a compact basic calendar: it does not mark holidays or prevent selection of dates in the past.

Open the picker and return a date to a cell

In the Visual Basic Editor, choose Insert > Module and add this macro to a standard module. It stores the target cell before opening the dialog, so the result goes to the original active cell. Run it with the cell you want to fill selected.

Option Explicit

Public Sub PickDateForActiveCell()
    Dim picker As frmDatePicker
    Dim target As Range

    ' Remember the destination before showing the modal form.
    Set target = ActiveCell
    Set picker = New frmDatePicker
    picker.Show vbModal

    If Not picker.WasCancelled Then
        target.Value = picker.SelectedDate
        target.NumberFormat = "m/d/yyyy"
    End If

    Unload picker
End Sub

Run the macro from the Macros dialog, or assign it to a worksheet button. For the latter, use Developer > Insert > Button (Form Control), draw the button, and select PickDateForActiveCell in the Assign Macro dialog. The Developer tab may need to be enabled through File > Options > Customize Ribbon. On Windows, Alt+F8 opens the Macro dialog and Alt+F11 opens the editor; on Mac, use Tools > Macro > Macros to manage macros, and the Visual Basic Editor shortcut can vary by keyboard and Excel settings.

The macro writes a real Excel date value, not a text label. Excel stores dates as serial numbers and displays them according to the cell’s number format. The example applies m/d/yyyy, but that format can display differently under regional settings; change it to an appropriate format such as yyyy-mm-dd if that suits the workbook. If the target cell is part of a table or has a required format, adapt the macro rather than assuming this display format is right for every file.

How the date selection works

When the form opens, it sets the displayed month to the current month and creates 42 day buttons, enough for six weeks. The first day’s weekday determines which positions remain disabled. For example, if the first falls on a Thursday, the first three positions are blank and disabled; day 1 appears under Thursday. The last unused positions in the grid are disabled in the same way.

When a day button is clicked, its Tag property supplies the day number to the form. The form combines that number with the currently displayed year and month using DateSerial, stores the result in SelectedDate, and hides itself. The calling macro checks WasCancelled before changing the worksheet. It starts as True, so closing the form with its X button does not accidentally write a date. The Today button is a deliberate selection and returns the system date.

Using a modal form (vbModal) means the user must close or complete the picker before continuing in Excel. That is useful for a simple cell-entry workflow and helps keep the destination stable. A modeless form is possible, but it requires more careful handling of focus, cell changes, and form lifetime.

Windows, Mac, and web compatibility

This implementation is aimed at desktop Excel for Microsoft 365 on Windows. It uses VBA UserForms and Microsoft Forms controls. Excel for the web cannot create or run VBA macros, so a workbook opened there will not provide this picker; users may still see the workbook’s saved cell values and formatting.

Excel for Mac supports VBA, but Windows-specific ActiveX controls and some UserForm behavior are not portable. This example avoids the legacy Date and Time Picker ActiveX control, but dynamically creating Microsoft Forms controls can still differ between Office platforms. If Mac users are important, verify the exact workbook in the Mac version you support before deployment. A worksheet-based calendar or a date-entry cell with validation may be a more portable choice. Keyboard shortcuts also vary on Mac, and many Windows Alt-key ribbon sequences do not transfer directly.

Macro execution is also governed by Trust Center settings and organizational policy. Do not tell users to enable macros in an unfamiliar workbook simply to access a date picker. Distribute a workbook from a trusted source, explain what its macro does, and follow the organization’s approved signing and deployment process where applicable.

Common problems and fixes

  • “Variable not defined” or a missing control error: Check the form name, class name, control names, and spelling. The procedures refer to frmDatePicker, cDayButton, and the five named form controls.
  • Buttons appear but do nothing: Confirm the click event is in the cDayButton class and that each hook is added to mDayHooks. The collection keeps the event objects alive while the form is displayed.
  • Dates appear as numbers: The stored value may be correct but the cell is formatted as General. Apply a date number format. A date serial is normal; it is not evidence that the picker returned text or failed.
  • The picker does not open: Confirm macros are enabled for this trusted workbook and that the macro is in a standard module. Excel for the web will not run it.
  • Month headings or date order seem unfamiliar: This grid deliberately starts weeks on Monday. Date display and weekday conventions can vary with locale; adjust the weekday labels and calculation together if you want a Sunday-first calendar.
  • Compile error involving MSForms: Check the Office VBA project’s references and confirm the Microsoft Forms library is available. Avoid downloading random replacement controls; use a supported Office installation or choose a simpler worksheet-based approach.

Before running any macro, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, including a value written to a cell by a macro. If a macro writes to the wrong place, close without saving or restore the saved copy rather than expecting Excel’s Undo command to reverse the change.

When to choose another approach

A UserForm is a good fit when users repeatedly enter dates into a controlled desktop workflow and the workbook can use macros. It is less suitable when recipients use Excel for the web, have macros disabled, or need a low-maintenance file that works identically across Windows and Mac. It also adds code that someone will need to maintain if the form layout or workbook process changes.

For simpler needs, consider these alternatives:

  • Data validation: Use Data > Data Validation and allow a date within a specified range. This can reject invalid entries, but it is not a popup calendar.
  • Direct date entry: Provide a clearly labeled date cell, an example format, and an appropriate number format. This avoids macros, though users must type the date.
  • Worksheet calendar: Put a calendar layout on a sheet and let users choose or copy a date. This can be easier to inspect and adapt than a UserForm, but takes worksheet space.
  • Office add-in or approved application: In managed environments, an approved add-in or a dedicated data-entry application may suit multi-platform workflows better. Check its deployment, privacy, and support requirements before choosing it.

Frequently Asked Questions

Does Excel have a built-in date picker for cells?
Excel for Microsoft 365 on Windows does not provide a universal built-in calendar popup for ordinary worksheet cells. Data validation can constrain date entries, but a VBA UserForm or another approved solution is needed for a calendar interface.

Will this VBA date picker work in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms. Use desktop Excel or choose a non-VBA workflow for users who work in a browser.

Can I use the old Date and Time Picker control?
It is not a dependable choice for current Excel workbooks. The legacy ActiveX control is missing from many installations and can have 32-bit, 64-bit, and platform compatibility problems. A VBA-built grid avoids that particular dependency.

Can I make the calendar open when a cell is selected?
Yes, a worksheet event such as Worksheet_SelectionChange can show a form when a particular cell is selected. That approach needs safeguards to prevent repeated popups while navigating and should be tested carefully; a button or explicit macro is simpler for many users.

Why does the cell show a number after I choose a date?
Excel stores dates as serial values and relies on number formatting for their appearance. Apply a date format to the destination cell, or set its NumberFormat in the macro as this example does. The appropriate display format depends on the workbook’s regional conventions.

Can I use the form on Mac?
Possibly, but do not assume a Windows UserForm implementation will behave identically on Mac. Test the controls and code on the exact Mac Excel versions in scope, or use a worksheet-based alternative if cross-platform reliability is essential.

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.