Date Picker in VBA UserForms: Setup and Alternatives

Excel for Microsoft 365 on Windows does not provide a built-in date picker control for VBA UserForms. To let users select dates in a form, use a custom calendar UserForm, a compatible legacy ActiveX control where available, or a text box with date validation; the custom calendar is usually the most portable choice.

What a VBA UserForm date picker does

A date picker is an interface for choosing a date rather than typing one from scratch. In a UserForm, it commonly appears beside a date text box: clicking a button opens a small calendar, and clicking a day puts that date into the text box. The form can then use the chosen value in a worksheet, report, or other macro task.

That convenience is useful for dates such as appointment days, invoice dates, or a reporting period. It can reduce typing mistakes, but it does not by itself guarantee that the date is valid for the task. A calendar may still allow a date outside an allowed range, for example, so the VBA code should check the value before using it.

Why there is no standard built-in control

VBA UserForms have common controls such as text boxes, labels, and command buttons, but Microsoft 365 for Windows does not include a universal, built-in calendar picker that can simply be added to every UserForm. The controls shown in the toolbox depend on the controls installed and registered on that computer. A date picker shown in an old tutorial may therefore be absent from a current installation.

This is different from formatting a worksheet cell as a date. A cell number format controls how a date value is displayed; it does not create a calendar popup in a UserForm. Likewise, Excel for the web does not run VBA UserForms at all. A workbook can store VBA, but users need a desktop version of Excel that supports macros to run that form.

The legacy Microsoft Date and Time Picker

Older Windows examples often use the Microsoft Date and Time Picker control, sometimes listed as Microsoft Date and Time Picker Control 6.0 (SP6). It is an ActiveX control associated with the legacy MSCOMCT2.OCX file, not a standard feature guaranteed to be present in Microsoft 365. On some older 32-bit Office installations it may be available; on many newer systems it is missing, not registered, or incompatible.

A major limitation is 64-bit Office: the old Date and Time Picker control is not available as a compatible 64-bit control. Installing or registering an old OCX file is not a sound general fix, and can create security, support, or deployment problems. The control may also fail when a workbook is opened on another computer, even if it works on the developer’s machine.

Consider the legacy control only when you control the Windows computers, have verified the exact Office architecture and control availability, and can support every user’s setup. It is a poor choice for a workbook that will be shared widely or maintained for years. Do not assume that seeing the control in the VBA toolbox means all recipients can use it.

Choosing an approach

Pick the simplest method that fits how people will use and share the workbook. These are the common choices:

Custom calendar UserForm: Best when users need a visual calendar and the workbook must work across supported desktop Excel installations. It takes more VBA to build and maintain.
Text box plus validation: Suitable when users can type a date and a clear format or input instruction is enough. It is simpler and avoids extra controls.
Legacy Date and Time Picker: Consider only for a controlled, compatible Windows environment. It is not a portable default for Microsoft 365.
Worksheet-based entry: If a UserForm is not essential, ask users to enter a date in a worksheet cell and validate it there. This avoids building a popup, though it is not itself a calendar picker.

For a custom calendar, the calendar form can use ordinary labels and buttons to display month names and day numbers. That means it does not depend on the old OCX, but the developer must implement month navigation, selected-day handling, and checks for dates outside the current month.

Building a basic date-entry UserForm

If a calendar is not required, a text box and an OK button provide a straightforward starting point. In the VBA editor, use Developer > Visual Basic on Windows, then choose Insert > UserForm. Add a TextBox named txtDate and a CommandButton named cmdOK. The following event checks that the entry is a date before writing it to a worksheet. Change the sheet name and destination cell to suit the workbook.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. With those controls on a UserForm, place this code in that form’s code module:

Private Sub cmdOK_Click()
    Dim selectedDate As Date

    ' Require an entry that VBA recognizes as a date.
    If Len(Trim$(Me.txtDate.Value)) = 0 Then
        MsgBox "Enter a date first.", vbExclamation
        Exit Sub
    End If

    If Not IsDate(Me.txtDate.Value) Then
        MsgBox "Enter a valid date.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If

    selectedDate = CDate(Me.txtDate.Value)
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = selectedDate
    ThisWorkbook.Worksheets("Sheet1").Range("A1").NumberFormat = "yyyy-mm-dd"
    Unload Me
End Sub

This example relies on VBA’s IsDate and CDate interpreting the typed text according to the computer’s regional settings. A value such as 04/05/2026 can mean April 5 or May 4 depending on the locale. If people in different regions will use the file, give an unambiguous instruction such as entering dates as day, month name, and year, or build a calendar that returns a genuine Date value rather than relying on ambiguous text.

How a custom calendar typically works

A small calendar UserForm usually has a label for the displayed month, buttons for the previous and next month, seven weekday headings, and up to 42 day buttons arranged in a grid. When the form opens, VBA calculates the first weekday of the displayed month and fills the grid with the appropriate day numbers. Selecting a day stores a Date value, closes the calendar, and passes that value back to the original form.

There are different ways to build that grid. A developer can place the buttons on the form in advance and update their captions and visibility, or create controls at runtime. Pre-placed controls are easier to inspect in the designer; runtime controls can reduce repetitive setup but require more event-handling code. In either case, the calendar needs rules for blank cells, days from adjacent months, and navigation across December and January.

It is helpful to decide whether the calendar should permit only a certain range, such as dates from today onward. Enforce that rule in the selection code and validate again when the main form saves the result. User-interface restrictions improve usability, but validation at the point where data is written is what protects the workbook’s data.

Compatibility and sharing considerations

VBA UserForms run in desktop Excel for Windows and, with platform-specific limitations, Excel for Mac. ActiveX controls are a Windows technology and should not be treated as a cross-platform solution; the legacy Microsoft picker is especially unsuitable for Mac and 64-bit Office. If Mac users are in scope, test the form on the supported Mac version and favor native VBA controls rather than Windows-only components.

Users also need to allow macros for the workbook to run. Excel for the web cannot run the UserForm, and organizational security settings may block macros or files from untrusted locations. For a shared workbook, document the supported Excel platforms and provide a usable alternative, such as a worksheet date field, for people who cannot run the form.

Store an actual date value in the worksheet, not only a formatted string. Excel stores dates as serial values and applies a number format to show them as dates. This makes sorting and date calculations more reliable than saving text that merely looks like a date.

Common problems and practical fixes

The control is missing from the toolbox: That is expected on many current installations. Use a custom form or text entry rather than assuming the legacy OCX can be added.
The form works on one PC but not another: Check for dependencies on ActiveX controls, Office bitness, macro settings, and platform differences. Test on a clean copy of the recipient’s supported setup.
Dates appear swapped: The entry may be interpreted under a different regional date convention. Use an unambiguous input format or return a Date value directly from a calendar.
A valid date is rejected: Check the actual text, regional settings, and any additional rules in the code. IsDate checks whether VBA recognizes an expression as a date; it does not check whether the date is appropriate for a particular business process.
The worksheet contains text instead of a usable date: Assign a VBA Date value to the cell and set its number format separately, as in the example, rather than writing a display string.

Frequently Asked Questions

Does Excel 365 have a built-in VBA UserForm date picker?
No. Microsoft 365 does not provide a universal built-in calendar picker for VBA UserForms. A custom calendar, a compatible third-party or legacy control, or validated text entry is needed.

Can I use the Microsoft Date and Time Picker in 64-bit Excel?
The legacy Microsoft Date and Time Picker control is not a compatible 64-bit Office control. For 64-bit Microsoft 365, plan on a custom calendar or another approach that does not depend on that OCX.

Can a VBA UserForm date picker work on a Mac?
A UserForm may be usable in desktop Excel for Mac, but Windows ActiveX controls such as the old Microsoft picker are not a portable Mac solution. Build with ordinary VBA UserForm controls and test on the Mac versions your users have.

Can users open a VBA date picker in Excel for the web?
No. Excel for the web does not run VBA macros or UserForms. Users need desktop Excel, or the workbook needs a non-VBA date-entry alternative.

Should the selected date be saved as text?
Usually not. Save a real date value in the cell, then apply a number format for display. A real value can be sorted, filtered, and used in date calculations more reliably than text.

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.