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.
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.
This section introduces the Date Picker utility of Kutools for Excel. After enabling this feature, click a date cell will pop up a calendar, you can easily replace the existing date with a new date with it. Please follows the below steps to apply this feature.
1. Click Kutools > Content > Enable Date Picker.
2. After enabling this feature, click on a date cell, and a calendar icon will pop up right to the cell.
3. Click the calendar icon to open the Date Picker dialog box, and then click a new date to replace the date in selected cell.
- Undo button: Click this button to undo replacing date;
- Close button: Click this button to close the Date Picker dialog box;
- This feature can only apply to cells containing date.
- 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?