How to pop up a calendar when clicking a specific cell in Excel?
Supposing there is a column range in a worksheet you need to frequently enter and change dates inside, but it is bored to enter or change date manually per time. How to quickly enter dates without manually typing into the column range? This article is talking about popping up a calendar when clicking on cells in a certain range, then inserting date into the selected cell automatically after selecting date in the calendar.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Please solve this problem as follows step by step.
Note: This method can only work on Microsoft Excel 32-bit.
Please create a UserForm which contains the calendar you will pop up by clicking on a cell.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > UserForm.
3. Then a UserForm and a Toolbox windows pop up, in the Toolbox, click any one control and right click, then select Additional Controls from the right-clicking menu. See screenshot:
4. In the Additional Controls dialog box, scroll down to check the Microsoft MonthView Control option in the Available Controls box, and then click the OK button.
5. Then you can see the MonthView button is added in the Toolbox window. Please click this MonthView button, and then click on the UserForm1 window to create a Calendar in the Userform.
Note: You can adjust the size of the UserForm window to suit the inserted calendar by dragging the border of the UserForm.
6. Double click the inserted calendar in the UserForm1, and in the Code window, please replace the original code with the following VBA script.
VBA code: create a user form with calendar
Private Sub MonthView1_DateClick(ByVal DateClicked As Date) On Error Resume Next Dim xRg As Object For Each xRg In Selection.Cells xRg.Value = DateClicked Next xRg Unload Me End Sub
Note: This code can help to insert date into selected cell after selecting date from calendar.
Now you need to specify certain cells to pop up calendar when clicking. Please do as follows.
7. Double click the sheet name which contains the cells you will click to pop up calendar in the left Project pane, then copy and paste the below VBA code into the Code window. See screenshot:
VBA code: Click cell to pop up calendar
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If (Target.Count = 1) Then If Not Intersect(Target, Range("A2:A10")) Is Nothing Then UserForm1.Show End If End Sub
Note: in the code, A2:A10 are cells you will click to pop up calendar. Please change the cell range as you need.
8. Press Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
From now on, when click on any cell inside the specified range in current worksheet, a calendar will pop up as below screenshot shown. And date will be inserted automatically into the selected cell after selecting date from the calendar.
- How to trigger or run a Macro by clicking a specific cell in Excel?
- How to change cell color when cell is clicked or selected in Excel?
- How to pop up message box when clicking on a certain cell in Excel?
- How to disable button after clicking once in Excel?
- How to disable right click on sheet tab in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoThe calendar will show up but when I click on the date, the cell doesn't populate
To post as a guest, your comment is unpublished.· 7 months agoThank you so much! These directions were super useful :)
To post as a guest, your comment is unpublished.· 9 months agoHi, Is it possible to put the date picker pop-up for multiple column, as in my sheet I have "start date", "end date" and "agreement date". if yes then how?
To post as a guest, your comment is unpublished.· 10 months agoSalve il codice funziona benissimo, ma se volessi farlo funzionare anche su un altro foglio
To post as a guest, your comment is unpublished.· 10 months agoif i try to select a row, the pop up will activate and the date appears in each cell in that row
how can i avoid this
To post as a guest, your comment is unpublished.· 11 months agotarihi seçebiliyorum ama a1:a10 hücrelerine seçtiğim tarih eklenmiyor. teşekkür ederim
To post as a guest, your comment is unpublished.· 2 years agoHello everyone,
Can anyone tell me how to popup a calendar in a range of cells, but starting only from the cell right bellow a table header and down bellow in an excel column.
Thank you in advance.
To post as a guest, your comment is unpublished.· 10 months agoUse i.e: Range("B6:C30")
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 2 years agoi used these VBA codes and everthing's fine so far. The range is A2:A10 and calendar pops up when you select a cell into it. But if you mark row from 2 to 10 again the calendar pops again. It's the same with column "A" if you mark it, again the calendar pops. How should i proceed, in order to get the calendar only in the range i've defined?
To post as a guest, your comment is unpublished.· 2 years agoI want to show pop up calendar under the cell selected.How should I do?