Yes, you can add a date picker to an Excel UserForm, but Excel for Microsoft 365 on Windows does not include a built-in calendar control that is available on every installation. The dependable choices are to create a calendar UserForm in VBA or, where compatible, use the older Microsoft Date and Time Picker ActiveX control.
What an Excel UserForm date picker does
A date picker lets someone choose a date from a calendar rather than typing it into a text box. In a VBA UserForm, the chosen date can be shown in a TextBox, written directly to a worksheet cell, or passed to other code for validation and processing. This can reduce typing mistakes and make a data-entry form easier to use.
It helps to distinguish a UserForm date picker from Excel’s worksheet date validation. Data validation can restrict a cell to dates within a permitted range, but it does not provide a pop-up calendar by itself. Excel for the web also does not run VBA UserForms. A UserForm date picker is therefore a desktop VBA feature, not a universal Excel calendar button.
Choose between a legacy control and a custom calendar
There are two common approaches, and the right one depends on who will open the workbook and which Excel versions and operating systems they use.
- Legacy Date and Time Picker control: Quick to place on a form if the control is already installed and compatible. It can be unsuitable for a workbook shared across different Windows installations or bitness.
- Custom VBA calendar: A calendar built from standard UserForm controls, such as labels and command buttons. It takes more work to create, but avoids relying on that particular ActiveX control.
- Third-party add-in or form: May provide a ready-made calendar, but introduces another component to install, trust, maintain, and check for compatibility.
For a workbook used only on one managed Windows computer, an available legacy control may be adequate. For a workbook distributed to colleagues, a custom calendar or a different data-entry method is generally easier to support. Confirm compatibility on the actual computers before choosing.
Using the Microsoft Date and Time Picker control
The legacy control is often called the Microsoft Date and Time Picker, or DTPicker. In the VBA editor, open the UserForm and look for Tools > Additional Controls or the Additional Controls option in the Toolbox. If a Microsoft Date and Time Picker entry appears, select it, add the control to the form, and set its properties in the Properties window. The exact displayed name can vary with the installed control.
When present, the control provides a calendar interface and a date value that VBA can read. For example, code can copy its selected value into a TextBox or worksheet cell. However, seeing instructions for this control online does not mean it is installed on a given computer. It is a legacy ActiveX component, not a standard control guaranteed to ship with current Excel installations.
Compatibility is the main drawback. Some versions of the control are 32-bit, and such a component will not load in 64-bit Excel. It may also be missing, unavailable in the Toolbox, blocked by an organization’s policies, or absent on another user’s computer. Avoid downloading an OCX file from an untrusted site to make the control appear; installation and registration can require administrator access and create security or support problems.
Building a custom calendar UserForm
A custom calendar usually consists of a month-and-year label, buttons to move backward and forward, weekday headings, and a grid of 42 day buttons (six rows of seven). When the form opens, VBA calculates the first day of the displayed month and fills the grid with the appropriate dates. Clicking a day records the selected date, then either returns it to the calling form or places it in a specified cell.
Useful design details include a clear or cancel option, a visible indication of today and the selected day, and rules for dates outside an allowed range. Decide whether the calendar should allow dates from adjacent months in the first and last grid rows. If it does, make those dates visually distinct so a user does not mistake them for dates in the displayed month.
The calendar’s logic needs to account for different month lengths, leap years, and the weekday on which a month starts. It should work with actual VBA Date values rather than building dates as text. A well-designed form can be reused by several entry forms, but the code needs a defined way to return the selected value, such as a public property or a designated TextBox.
A custom calendar is not automatically compatible with every environment merely because it avoids the DTPicker. It is still VBA and still requires a desktop version of Excel that can run the workbook’s macros. Test the form in each supported Excel version and operating system, especially if it uses Windows-specific controls or APIs.
Connect the selected date to a worksheet
One simple pattern is to let the calendar put its result in a date TextBox on the entry form, then have an OK button write that value to the worksheet. In this example, the form contains a TextBox named txtDate and a CommandButton named cmdOK. The calendar itself must first populate txtDate; this click procedure checks the value and writes it to cell A2 on a sheet named Sheet1.
Private Sub cmdOK_Click()
Dim chosenDate As Date
' Stop if the entry is not a recognizable date.
If Not IsDate(Me.txtDate.Value) Then
MsgBox "Enter or select a valid date.", vbExclamation
Exit Sub
End If
chosenDate = CDate(Me.txtDate.Value)
ThisWorkbook.Worksheets("Sheet1").Range("A2").Value = chosenDate
ThisWorkbook.Worksheets("Sheet1").Range("A2").NumberFormat = "yyyy-mm-dd"
Unload Me
End Sub
Put this event procedure in the relevant UserForm’s code module, and change the sheet, cell, and control names to match the workbook. The displayed format is ISO-style year-month-day, but the cell contains a genuine Excel date value rather than text. That matters for sorting, date arithmetic, and filtering. IsDate and CDate interpret text according to the computer’s regional settings, so a typed value such as 03/04/2026 can be ambiguous. A calendar selection avoids much of that ambiguity.
Save a backup copy before running or changing VBA. Macros cannot be undone with Ctrl+Z, including a macro that writes a value into a worksheet cell.
Design the date-entry rules carefully
A calendar makes choosing a date easier, but it does not decide which dates are valid for the task. For an appointment form, past dates might be disallowed; for an invoice history form, past dates may be essential. Put those rules in the form’s validation code as well as presenting them clearly in the interface. Do not rely on the calendar’s appearance alone to enforce a business rule.
- Decide whether the form accepts only dates or also a time of day.
- Set minimum and maximum dates when the task requires a bounded range.
- Choose a display format that is clear to the people using the workbook.
- Handle Cancel separately from selecting a date, so cancellation does not accidentally write an old or default value.
- Check that the target cell is available and that the worksheet is not protected in a way that prevents writing.
Formatting a cell changes how a date appears, not the underlying date value. Conversely, storing a date as text can make later sorting and calculations unreliable. If the workbook exchanges data with systems that expect a particular format, define that format at the point of export rather than assuming every user’s regional settings are the same.
Windows, Mac, and Excel for the web
The legacy ActiveX control is a Windows dependency and is not a cross-platform choice. Excel for Mac does not support ActiveX controls. Mac Excel can run VBA, but UserForm behavior and available controls may differ from Windows, so a custom form intended for Mac users needs testing on Mac rather than being assumed compatible.
Excel for the web cannot run VBA macros or open VBA UserForms. A workbook that depends on a UserForm therefore needs to be used in desktop Excel for the data-entry workflow to work. If recipients need browser-based entry, consider a web form or another supported input process instead of asking them to open the workbook in a browser.
On Windows, open the VBA editor with Alt+F11 in most Excel setups, or use the Visual Basic command on the Developer tab. Keyboard mappings can vary on compact keyboards. On Mac, the shortcut can depend on the keyboard and function-key settings; use Excel’s Developer or Tools menu to open the Visual Basic editor if the shortcut does not work.
Common problems and troubleshooting
- The control is missing: It may not be installed or registered, or the Excel installation may not support that version. Do not assume a workbook can supply the control simply by being saved.
- The form works on one computer only: Check Excel bitness, operating system, installed components, and macro policies on the computer where it fails. A dependency on a legacy OCX is a frequent cause.
- The wrong date appears: Check whether the code is interpreting typed text using regional settings. Prefer a selected Date value and inspect the destination cell’s number format.
- Clicking a day does nothing: Confirm that the button’s event code is attached to the correct control and that the selected value is passed back to the entry form.
- The form will not run: Confirm the workbook is macro-enabled, macros are permitted by the organization, and the workbook is opened in desktop Excel rather than Excel for the web.
When troubleshooting, test with a small copy of the workbook and a known date such as 15 January 2026. That date is useful in testing because it avoids the common day-month ambiguity in dates where both parts are 12 or lower. Check both what the form displays and the actual value and number format in the destination cell.
Frequently Asked Questions
Does Excel have a built-in date picker for UserForms?
No universally available built-in calendar control is included for VBA UserForms in Excel for Microsoft 365. Use a custom calendar, a compatible legacy control, or another data-entry approach.
Can I add a calendar to a worksheet cell without VBA?
Excel’s date data validation can limit acceptable entries, but it does not by itself create a pop-up calendar. A calendar interface requires an additional feature, add-in, or VBA-based solution, depending on the Excel version and environment.
Will the Date and Time Picker work in 64-bit Excel?
Not necessarily. Some legacy versions are 32-bit and cannot be loaded by 64-bit Excel. Check the installed control and Excel bitness on the target computer rather than relying on a workbook author’s setup.
Can a UserForm date picker work in Excel for the web?
No. Excel for the web does not run VBA UserForms. Users need desktop Excel for that workflow, or a separate browser-compatible way to enter dates.
What is the safest choice for a workbook shared with colleagues?
A custom calendar built from standard VBA UserForm controls avoids dependence on the legacy DTPicker, but it still requires desktop Excel and permitted macros. If users need Mac or web access, test a suitable cross-platform input method instead.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Create Word Document From TemplateTo create a Word document from a template with VBA, call Word’s Documents.Add method with the template’s full…
- Excel VBA List Worksheet Names: Methods and ExamplesTo list worksheet names with Excel VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name…
- VBA Len Function: Count String Characters in ExcelIn VBA, Len returns the length of a string: Len("VBA Len") returns 7, including the space. Use it…
- Import CSV to Excel Using VBA: A Practical GuideTo import a CSV file into Excel with VBA, use Workbooks.OpenText to parse the file, then copy its…
- Excel VBA Active Pivot Table: How to Find and ControlIn Excel VBA, get the PivotTable associated with the active cell by using ActiveCell.PivotTable; Excel does not provide…
- Basic For Loop in VBA: Syntax, Examples, and Usage GuideA basic For loop in VBA repeats a block of code a set number of times. Write For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.