Show pop-up calendar upon right click [Excel VBA]

Posted on November 13th, 2013 in VBA Macros - 30 comments

This is a guest post by Vijay, our in-house VBA Expert.

There are times when we are entering dates into several columns and would like to select a date from a popup calendar instead of manually typing.

Today, lets understand how we can set up a pop-up calendar in Excel so that your users can easily input dates by right clicking on a cell and inserting a date.

Keep in mind:

1. This code is only supported on the 32-Bit versions of Excel.
2. You need to have admin rights to be able to install the ActiveX Control

First, take a look at pop-up calendar

Here is a short demo of how our pop-up calendar behaves.

right_click_context_menu_example

What we need to do this

1. Design user form that contains our calendar.
2. Create a Data Table
3. Put some VBA code to get this done

Design user form that contains our calendar.

First let’s design the user form, so start up Excel and bring up the Visual Basic editor and add an user form in the project.

We would need the Microsoft Date and Time Picker control for this project, so please ensure that you have the required file available on your system. If it is not available you may download the MSCOMCT2.OCX from this link.

http://activex.microsoft.com/controls/vb6/mscomct2.cab

Installing this file is pretty simple, you need to extract the contents form the CAB file and then copy this into your System32 folder and then register using the REGSVR32 utility.

If you are using Windows 7 or above you would need to copy this file into the SysWOW64 folder and then register.

For Windows 7 and above, please make sure you are running the Command Prompt (Admin) to be able to successfully register the ActiveX control.

Windows 7: Click on Start, All Programs, Accessories, Command Prompt (right click and choose Run as Administrator
Windows 8: Windows Key + X, then choose Command Prompt (Admin)

command_prompt_example

Okay, let’s get back to designing the user form.
Insert a new Userfrom on the VBA project and then click on Addition Controls on the Tools menu.
additional_controls
Once the Additional controls dialog box is on the screen, locate the above highlighted entry and then select the same by clicking the box on the left. Now click Ok to close this dialog box.
Now place one Monthview control on the userform and one Command button.
Below are the properties that we need to change for the Commandbutton
• Caption = “Close”
• Cancel = True
• Name = cmdClose
Place this command button anywhere you like on the userfrom, we will place the Monthview on top of this to avoid show this to the user.
Since we have specified the Cancel = True for the commandbutton, the click event can be triggered by pressing the Escape key to handle the code that we will write for the Close button.

Now place the Monthview control as show in the picture below
userform

We are done designing the Userform, now we need to write the code to handle the events.
Below is the code
Close Button

Private Sub cmdClose_Click()
Unload Me
End Sub

Userfrom

Private Sub UserForm_Initialize()
'matching the date in the calendar with the date of the active cell
'if there is a date,
If IsDate(ActiveCell.Value) Then
Me.MonthView1.Value = ActiveCell.Value
Else
Me.MonthView1.Value = Now
End If
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
On Error Resume Next
Dim cell As Object
For Each cell In Selection.Cells
cell.Value = DateClicked
Next cell
Unload Me
End Sub

What the above code does?

1. The close button code will simply unload the userform and take it off the screen.
2. The userform initialize event code will check if the current cell on which we are right clicking the mouse contains any date, if there is a date then it will set the date on the calendar as the one on the cell, otherwise it will show today’s date.
3. The dateclick event of the Monthview control occurs when we click on any date, this code is responsible for populating the cell with the date we have selected. If there are multiple cells selected the code will populate all of them with the date selected.

Adding the context menu option

Now comes the interesting part of adding the context menu option, one thing I would like to specify here as the name suggests “Context menu” these options change depending on what and where we are right clicking the mouse. You will see a different context menu when you right click on a cell, table, shape etc. as shown in the example below
sample_right_click_context_menu

Since every object has a different type of context menu associated we need to make site we are adding our option to the right place.
I would recommend reading this article to know more about the types of commandbars available and how to use them. http://msdn.microsoft.com/en-us/library/office/aa141001(v=office.10).aspx

Also this link provides a list of available names http://www.mrexcel.com/forum/excel-questions/525939-visual-basic-applications-list-available-commandbars-excel-2010-a.html

We wanted to add the right click context option to a data table which is called as “List Range Popup”.

Create a Data Table

Type the heading in Cells
B2 = ID
C2 = Start Date
D2 = End Date
E2 = Name

Now click on cell B2, and press CTRL + T shortcut from the keyboard. Make sure to select the option My Table has headers and then click Ok.

We would need the add the below code to the Open event of our workbook so that this option is available to us every time we need to work here.


Private Sub Workbook_Open()
On Error Resume Next
Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "Module1.OpenCalendar"
Application.CommandBars("List Range Popup").Controls("Insert Date").Delete
Set NewControl = Application.CommandBars("List Range Popup").Controls.Add(Before:=1)
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub

We have also assigned a shortcut key of CTRL + SHIFT + C to this, for those who love to work more using the keyboard.

The above code will add the “Insert Date” context menu option to our data table(s) in the active workbook whenever we open this file.

Next is cleanup
We need to make sure that the context menu we have added is also removed when the file is close, the below code will do that for us.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("List Range Popup").Controls("Insert Date").Delete
End Sub

Note: I have seen the project code left over in the VBA project explorer even after we have close this file, and did some research on the same. The common reason for this is having some COM addins installed. Please share if you also run into this issue and if you were able to find any other reasons or ways to eliminate this issue.

Download Demo File


Click here to download the demo file
& use it to understand this technique.

What about you? Do you use them often? Please share your experiences, techniques & ideas using comments.

If you are new to VBA, Excel macros, go thru these links to learn more.

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

About Vijay

Vijay (many of you know him from VBA Classes), joined chandoo.org full-time this February. He will be writing more often on using VBA, data analysis on our blog. Also, Vijay will be helping us with consulting & training programs. You can email Vijay at sharma.vijay1 @ gmail.com. If you like this post, say thanks to Vijay.

Your email address is safe with us. Our policies

Written by Vijay Sharma
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

30 Responses to “Show pop-up calendar upon right click [Excel VBA]”

  1. ki1418 says:

    I dont understand VBA. I will try this, and ask u if I have any problem . Thanks!

  2. Athman says:

    Hi Vijay,

    Thanks very much. I enjoyed your post.

  3. Venky says:

    Vijay

    Thanks. Looking forward for more on VBA.

  4. Rob T says:

    One of the biggest drawbacks I encounter whenever I use VBA for updating values is that it is not compatible with the undo function.
    So in this case for example, a user realises they had multiple cells selected by mistake and inserts the same date into all of them. They cannot go back to the previous values because there’s no undo!
    For this reason, I’ll generally only use vba for my own purposes (where I know to save our double-check before I run it), and avoid it for other users.

    • Greg says:

      try this.

      Private Sub Monthview1_DateClick(ByVal DateClicked As Date)
      On Error Resume Next

      Dim Cell As Object

      For Each Cell In Selection.Cells
      If Selection.Count > 1 Then
      MsgBox (“Please select only one cell.”)
      Unload Me
      Exit Sub
      Else
      Cell.Value = DateClicked
      End If

      Next Cell
      Unload Me
      End Sub

      • Rob T says:

        Ok, that’s a solution to my example of having lots of cells selected, but my general point still stands.

        Say you realise that something you did shortly before using the macro was a mistake. Normally, you could undo a few steps and then repeat the things you did since that event. But as soon as you run vba, it wipes the whole undo history.

  5. Greg says:

    what commandbars should i be using if i want to add it to my “Cell” menu option instead of a “Table Cell?”

  6. Eric says:

    Did you forget the OpenCalendar sub?

  7. Adrian says:

    Excellent post. I spent last week working on a similar form, and it was the first time I worked with Calendar dates. This is a great refresher.

  8. Rodney says:

    The Microsoft Date and Time Picker control does not seem compatible with a 64-bit install of Excel 2013.

  9. Tom says:

    Thanks for the lesson. I look forward to using the classes on vba.

  10. Gino says:

    The only problem with mscomct2 is that anyone you share the workbook with needs to also install it. And most of my users have a hard time typing cmd! At least in Excel 2010. Major bummer…

  11. jules says:

    How would I put this in a user form that is created for data entry. Can you but a userform in a userform?
    It’s very nice.

  12. Jam says:

    This is impressive Chando. but it does not work without having a table. how the code could be improved where in the worksheet any cell the data picker right click works without having a table?

    thanks.

    • Vijay Sharma says:

      Jam,

      I have shared a link above that displays the list of available options of names such as “List Range Popup”, “cells” etc for which you may add the context menu options. Please go through the same.

      ~VijaySharma

  13. Jack says:

    Will this still work if you send the spreadsheet to somebody who doesn’t have the MSCOMCT2.OCX installed on their computer?

  14. fiasco says:

    As a vba veteran, you can write a routine that will automatically add the correct reference libraries to a users pc when they open the workbook to start with. A bit of code but it works…

  15. Vinod James says:

    Can any one please tell where to found “syswow64″ folder in windows 7

  16. Vinod James says:

    Got it in Windows 7 32 Bit Copy those files in
    C:\windows\system32
    then give command
    C:\windows\system32\mscomct2.ocx

    But is it necessary to have Visual basic install in computer

  17. […] Show pop-up calendar upon right click [Excel VBA] | Chandoo.org – Learn Microsoft Excel Online seems to be an answer for both of you; though i have not read tried that yet. Good Luck. […]

  18. Sam Snow says:

    I am currently running Win7 64bit, MS Excel 2010. I tried to get this calendar to work but didn’t realize until later that this method is intended for the 32bit OS. Is there a way I can get this great-looking calendar to work on Win7 64bit?

  19. Kat says:

    Thanks so much for the tutorial! I’m completely new the the excel programming and I found it very helpful. However, while everything is working (on the first trial!), my pop up calendar doesn’t show up next to my cell but off to the left side of the screen all the time. Have anyone had similar problem in the past? Do you know where the problem might be?

  20. Arturo says:

    I got this working in my data entry spreadsheet that I use to collect information. This pop up calendar works great in my PC, but in many other users they get the error that the “Method or data member not found” because the MonthView control is not enabled in their PCs.
    I cannot give directions to everyone on how to install the control … is there a way to have it “autoinstalled”? … or is there another alternative to having a pop up calendar without the hassle of installing the control first?
    thanks in advance.

Leave a Reply