Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Pop up a calendar when clicking a specific cell with VBA code

Create a UserForm with calendar

Activate the Calendar when clicking on cell


Date picker (easily select date with specific date format from calendar and insert to selected cell):

Click Kutools  > Insert > Insert Date, and in the Insert Date dialog box, specify a date format, double click a certain date to insert this date into a selected cell immeidately. See screenshot below:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!


Pop up a calendar when clicking a specific cell with VBA code


Please solve this problem as follows step by step.

Note: This method can only work on Microsoft Excel 32-bit.

Create a UserForm with calendar

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.

Activate the Calendar when clicking on cell

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.


Related articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    DwarthK · 19 days ago
    Thank you so much! These directions were super useful :)
  • To post as a guest, your comment is unpublished.
    jeet · 2 months ago
    Hi, 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.
      crystal · 2 months ago
      Hi jeet,
      Follow the steps and replace the range "A2:A10" in the second VBA code with your column range (such as C2:E2).
  • To post as a guest, your comment is unpublished.
    Giuseppe · 3 months ago
    Salve il codice funziona benissimo, ma se volessi farlo funzionare anche su un altro foglio
  • To post as a guest, your comment is unpublished.
    Sam · 4 months ago
    if 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.
      crystal · 3 months ago
      Hi Sam,
      The code has been updated in the article with the problem solving. Please have a try and thank you for your comment.
  • To post as a guest, your comment is unpublished.
    cevat · 4 months ago
    tarihi seçebiliyorum ama a1:a10 hücrelerine seçtiğim tarih eklenmiyor. teşekkür ederim