Excel for Microsoft 365 on Windows does not include a universal VBA calendar date-picker control that you can simply add to any worksheet cell. The usual VBA approach is a custom UserForm, or the older MonthView ActiveX control if it is available in your Office installation; for many current workbooks, date validation or an add-in is the more compatible choice.
What a VBA date picker does—and what Excel provides
A calendar date picker lets someone choose a date visually rather than typing it. In an Excel workbook, that usually means showing a small form when a user selects a cell or clicks a button, then writing the chosen date into a worksheet cell. The form can also restrict choices, such as allowing dates only within a booking period.
Excel does not provide a standard pop-up calendar for every date-formatted cell, and setting a cell’s number format to Date does not add one. VBA can respond to worksheet events and display a form, but the calendar interface itself must come from a control, custom code, or an add-in. That distinction matters: instructions that say “insert a date picker” may rely on a legacy control that is missing from a newer or 64-bit installation.
VBA macros run in the desktop Excel application when macros are enabled. Excel for the web does not run VBA, so a workbook that depends on a VBA calendar cannot offer that picker in the browser.
Choose an approach for your workbook
Before writing code, decide whether users truly need a clickable calendar. A date picker is useful for occasional users, touchscreens, or forms where dates must follow clear business rules. It can be unnecessary overhead for a small worksheet where users are comfortable typing dates.
- Custom VBA UserForm: Offers the most control over appearance and rules, but requires building and maintaining the calendar interface.
- MonthView ActiveX control: Can provide a ready-made calendar in some Windows installations, but availability and bitness compatibility are major limitations.
- Data validation: Restricts acceptable date entries without creating a calendar. It is often simpler to share and maintain.
- Office add-in: May provide a calendar interface without workbook VBA, but check its publisher, privacy terms, compatibility, and licensing.
For a workbook shared across different computers, choose based on the recipients’ Excel versions and operating systems—not just the machine used to build it. A control that works for one person can fail to load for another.
Using the legacy MonthView control
Some Windows installations of desktop Excel expose a control called Microsoft MonthView Control 6.0 (SP6), commonly associated with the legacy MSCOMCT2.OCX library. If it is installed and available, it can be placed on a VBA UserForm and used to select a date. In the VBA editor, create a UserForm, open the Toolbox’s Additional Controls list, and look for the MonthView control. The exact list depends on the installed Office components and system configuration.
If the control is available, set the form’s (Name) property to frmCalendar and the control’s (Name) property to MonthView1. Add this event procedure to the form’s code window:
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
' Write the selected date to the currently selected cell.
ActiveCell.Value = DateClicked
ActiveCell.NumberFormat = "m/d/yyyy"
Unload Me
End Sub
To open the form, add a standard module and use a macro such as this one. Assign the macro to a worksheet button if you want users to open the calendar with a click:
Public Sub ShowCalendarPicker()
' Display the form that contains MonthView1.
frmCalendar.Show
End Sub
Save a copy of the workbook before running a macro; VBA changes cannot be undone with Ctrl+Z. The example writes to whichever cell is active when the date is clicked, so a button or other workflow should make the intended target cell clear. A production form may store the target cell when it opens and validate it before writing.
Why the MonthView control often is not an option
MonthView is a legacy ActiveX control, not a built-in feature guaranteed in Microsoft 365. It may not appear in Additional Controls, and installing or registering an old control can require system-level changes. In particular, a 32-bit ActiveX control cannot be loaded into 64-bit Excel. Mac Excel does not support Windows ActiveX controls, and Excel for the web does not run VBA forms.
Do not download an OCX file from an unknown site just to make a calendar appear. Such files can create security and reliability risks, and registering a control does not make an incompatible 32-bit control work in 64-bit Excel. If a workbook must run on several Windows computers, confirm the control is installed and compatible on each one before relying on it. If recipients use Mac or web Excel, choose a different design.
Building a custom UserForm calendar
A custom UserForm avoids dependence on MonthView, but it is more than a few lines of VBA. A usable calendar needs a way to navigate months, show weekday headings, display the correct number of days, handle months that begin on different weekdays, and return the selected date. It should also define what happens when the user closes the form without choosing.
A common design uses a label for the displayed month, Previous and Next buttons, and a grid of 42 day buttons—six rows of seven days. Each button represents a calendar day; dates outside the displayed month can be blank or visually subdued. A selection handler combines the displayed year, month, and day into a VBA Date value, then returns that value to the calling worksheet or form. Developers often use a small class module with a WithEvents button reference so one event handler can manage all the day buttons.
Keep the selected destination separate from the calendar display. For example, store a reference to the cell that opened the form, then write the chosen date to that stored cell. This prevents the result from going into a different cell if the selection changes while the form is open. Also consider a clear action, a Cancel button, keyboard navigation, and a date range rule if the calendar supports bookings or deadlines.
Test month boundaries carefully: February in leap and non-leap years, months beginning on Sunday or Monday depending on the chosen week layout, and dates near the allowed minimum or maximum. A date picker should return a real Excel date serial, not a string that merely looks like a date. Apply a number format to control display while retaining the underlying date value for sorting and calculations.
Simple alternative: restrict dates with Data Validation
If the goal is to prevent invalid entries rather than provide a visual calendar, Excel’s built-in Data Validation is often enough. Select the input cells, then choose Data > Data Validation. On the Settings tab, set Allow to Date and specify a rule such as “greater than or equal to” a start date. You can set an input message and an error alert so users understand what is allowed.
For example, a date-entry column might allow dates from January 1, 2026, onward. This rule checks typed entries, but it does not create a calendar pop-up. It is still useful for shared workbooks because it avoids ActiveX dependencies and works as ordinary worksheet validation. Users can type a date using a format their regional settings recognize; the cell can then display it in a consistent format through the Home tab’s number-format controls.
Validation is not a security boundary: pasted data or other changes can sometimes bypass expected checks. If the workbook feeds a critical process, add suitable review or error checking rather than treating validation as a guarantee that every value is correct.
Windows, Mac, and web compatibility
The MonthView instructions apply only when the control is present in compatible Windows desktop Excel. VBA itself is available in desktop Excel for Mac, but Windows ActiveX controls are not a cross-platform solution. A custom VBA UserForm may be a better starting point for a desktop-only workbook intended for both platforms, but check any controls and code against the Mac version you need to support.
Excel for the web cannot run VBA macros or display VBA UserForms. A recipient can generally open a macro-enabled workbook in the browser, but the calendar behavior will not run there. If browser use is essential, consider an Office add-in or a workflow that uses date validation and normal cell entry instead. Keep a clear note in the workbook explaining how users on unsupported platforms should enter dates.
Common problems and practical checks
- The control is missing: MonthView may not be installed or registered, or the Office installation may not expose it. Do not assume every Microsoft 365 installation includes it.
- The form opens but the date goes to the wrong cell: Store the target cell when the form opens, rather than relying on ActiveCell after users interact with the form.
- Dates appear swapped: Text such as 04/05/2026 can mean April 5 or May 4 depending on regional settings. Returning a Date value avoids treating the choice as ambiguous text; formats affect display, not the stored date.
- The macro is blocked: The workbook must be saved in a macro-enabled format such as .xlsm, and the user’s security settings must permit the macro. Do not tell users to enable macros in files they do not trust.
- The picker works only on one computer: Check Excel bitness, operating system, installed controls, and macro settings on every supported setup. A custom form or non-VBA alternative may be more dependable.
When debugging, first confirm the form and control names match the names used in the code. Then verify that the event procedure is in the UserForm’s code module, not a standard module. Finally, test with a blank worksheet cell and a known date, and confirm the cell contains a date value that sorts chronologically.
Frequently Asked Questions
Does Excel have a built-in calendar date picker for cells?
Not a universal pop-up calendar for ordinary worksheet cells in Microsoft 365. Excel can format and validate dates, while a picker requires a form, compatible control, or add-in.
Can I use a VBA date picker in 64-bit Excel?
A 32-bit MonthView ActiveX control will not load in 64-bit Excel. A custom VBA form that does not depend on that control may work in desktop Excel, subject to testing and macro settings.
Will a VBA calendar work in Excel for Mac?
Windows ActiveX MonthView does not work on Mac. VBA and forms have platform limitations, so test a custom form on the Mac version you intend to support rather than assuming Windows controls will transfer.
Will the date picker work in Excel for the web?
No. Excel for the web does not run VBA macros or VBA UserForms. Use a browser-compatible add-in or a date-entry workflow that does not depend on VBA.
Does Data Validation add a calendar to the cell?
No. Data Validation can restrict which dates are accepted and show instructions or an error alert, but it does not itself display a calendar for selecting a date.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Import CSV Files: Methods, Code, and Common IssuesTo import a CSV file with VBA, use Excel’s Workbooks.OpenText method to parse it with the correct delimiter…
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- VBA Delete Row If Cell Contains TextTo delete rows when a cell contains particular text, use VBA’s InStr function in a loop that checks…
- Excel VBA: Hide a Column RangeTo hide a range of columns with Excel VBA, set its Hidden property to True: use Columns("B:D").Hidden =…
- VBA Sort Range by Column: Practical Examples and TipsTo sort a range by one column in Excel VBA, call the range’s Sort method and specify the…
- Excel VBA: Get the Current Workbook File Path EasilyIn Excel VBA, use ThisWorkbook.Path to get the folder path of the workbook that contains the code, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.