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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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?
Recommended Productivity Tools
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.· 19 days agoThank you so much! These directions were super useful :)
To post as a guest, your comment is unpublished.· 2 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.· 3 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.· 4 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.· 4 months agotarihi seçebiliyorum ama a1:a10 hücrelerine seçtiğim tarih eklenmiyor. teşekkür ederim