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, captionedToday. - A CommandButton named
cmdCancel, captionedCancel.
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
cDayButtonclass and that each hook is added tomDayHooks. 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
- Excel Slicers for Multiple PivotTables: How to Connect
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Import CSV Data the Right WayTo import a CSV file with Excel VBA, use Workbooks.OpenText to parse it, then copy its values into…
- Excel VBA Error Message Box: Show and Handle ErrorsAn Excel VBA error message box is usually created with the MsgBox function; use On Error GoTo to…
- Google Sheets Macros: Examples and How to Use ThemGoogle Sheets macros are saved actions you can replay to automate repetitive work, such as formatting a report,…
- Find Last Empty Row in Excel VBA: Reliable MethodsTo find the next empty row after the data in an Excel column, use End(xlUp) from the bottom…
- Excel VBA Or Operator: How It Works and When to Use ItIn Excel VBA, the Or operator combines conditions and returns True when at least one condition is True.…
- Excel VBA: Find a Value in a Column and Return Its RowTo find a value in a column with Excel VBA, use the Range.Find method on the cells you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.