Forum Discussion
How to add pop-up calendar in spreadsheet cell
Hi
How can I add calendar that pops-up on clicking on a cell in excel spreadsheet and allows to select the date.
Thank you.
- EternallyConfusedCopper Contributor
PB-04 so if you haven’t already sorted it, ive worked out, date picker is built in to 365 excel differently.
if you highlight cells you want the dates in, right click and click on ‘Number Format…’. Select date from the ‘Category’ list and your preferred date layout from the ‘type’ list. It should then add it in with the option for a pop up calendar when you click or double click on the cell
i couldn’t find any answers just alot of the old method and people saying it can’t be done without an add in. My job blocks add in’s so this is how i got it to work.
- PB-04Copper Contributor
Hi EternallyConfused, Thank you for the answer.
Is it available in Mac as well? - crazysidewaysdogCopper Contributor
EternallyConfused Doesn't work on Excel for Mac 16.86 (24060916) 365
- GeoffW71Copper Contributor
Thank you for your updaets however i cannot click and get the celander .. ive followed exactly am i doing something worng ??
- NikolinoDEGold Contributor
In Excel, you can add a pop-up calendar by using the Date Picker control from the Developer tab. Here's a step-by-step guide:
Enable the Developer Tab:
- Open Excel and go to the "File" tab.
- Click on "Options."
- In the Excel Options dialog box, select "Customize Ribbon."
- In the right column, check the box for "Developer" to enable the Developer tab.
- Click "OK" to apply the changes.
Insert Date Picker Control:
- Go to the "Developer" tab in the Excel ribbon.
- If you don't see the Developer tab, make sure you've enabled it as described above.
- Click on "Insert" in the Controls group.
- In the "Form Controls" section, click on the "Date Picker" (it might be labeled as "More Controls" in some versions).
- Your cursor will change to a crosshair. Click and drag on the cell where you want the pop-up calendar to appear.
Configure Date Picker Properties:
- Right-click on the Date Picker control, and choose "Format Control."
- In the Format Control dialog box:
- Under the "Control" tab, you can set the linked cell (where the selected date will be placed).
- You can also customize the date format in the "Format" section.
- Click "OK" to apply the changes.
Use the Date Picker:
- Click on the cell linked to the Date Picker control.
- A small calendar icon will appear next to the cell.
- Click on the calendar icon to open the pop-up calendar.
- Select the desired date from the calendar.
The selected date will be inserted into the linked cell, and you can use this cell value in your calculations or data.
Note: The availability of the Date Picker control depends on your version of Excel and your system settings. If you can't find the Date Picker, you may need to use alternative methods or third-party solutions. Additionally, some Excel versions might have the "Date and Time Picker" control instead of the "Date Picker." The text and steps were edited with the help of AI.
In order to provide more help, more information is required, such as Excel version, operating system (Win., Mac, etc.), storage medium (Sharepoint, OneDrive, etc.), file extension (.xlsx, xlsm, etc.).
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- PB-04Copper ContributorHi NikolinoDE,
Thank you for the quick reply with detailed instructions.
I have enabled developer tab but I don't see insert/ control group or any date picker option under the developer tab.
I am using Office 365 on MacBook Pro.
Please help. Thank you.- NikolinoDEGold Contributor
In Excel, you can add a pop-up calendar to a cell using the "Date Picker" control. But so far I know, Excel for Mac does not have a built-in date picker like Excel for Windows. However, you can use a workaround by creating a user form with a date picker control.
Here are the general steps:
- Open Excel:
- Launch Excel on your MacBook Pro.
- Enable Developer Tab:
- If you don't have the Developer tab enabled, go to "Excel" > "Preferences" > "Ribbon & Toolbar" and check the "Developer" option.
- Open the Visual Basic for Applications (VBA) Editor:
- Click on the "Developer" tab in the ribbon.
- Click on "Visual Basic" to open the VBA Editor.
- Insert UserForm:
- In the VBA Editor, right-click on any item in the project explorer on the left.
- Choose "Insert" > "UserForm" to add a new UserForm.
- Add a Date Picker Control:
- In the Toolbox (if it's not open, press Ctrl + T to open it), find the "Date Picker" control.
- Click and drag the Date Picker control onto the UserForm.
- Code the Calendar Behavior:
- Double-click on the UserForm to open the code window.
- Use VBA code to show the UserForm when the cell is clicked.
Here's an example:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$1" Then ' Change this to the cell address where you want the calendar UserForm1.Show Cancel = True End If End Sub
- This example assumes the cell A1 is the target cell for the calendar. Adjust the code based on your specific cell.
- Close the VBA Editor:
- Close the VBA Editor and return to Excel.
- Test the Calendar:
- Double-click the designated cell (e.g., A1), and the UserForm with the date picker should appear.
Keep in mind that Excel for Mac might have received updates since my last knowledge, and there might be new features or changes. Of course you can still create a simple date picker using Data Validation.
Additional Link/info: Date Picker Calander Pop up in a Cell in 365 for Mac?
NOTE: My knowledge of Office 365 on MacBook Pro is limited, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.