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

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.

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)

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.

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

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

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.

• What is VBA & Macros? Introduction
• Excel VBA Example Macros
• VBA tutorial videos

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.