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.
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.
41 Responses to “Show pop-up calendar upon right click [Excel VBA]”
I dont understand VBA. I will try this, and ask u if I have any problem . Thanks!
Hi Vijay,
Thanks very much. I enjoyed your post.
Vijay
Thanks. Looking forward for more on VBA.
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.
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
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.
what commandbars should i be using if i want to add it to my "Cell" menu option instead of a "Table Cell?"
I got it, instead of using ("List Range Popup"), i changed it to ("Cell") and now it works.
Did you forget the OpenCalendar sub?
Yes, i see the same step missing. added it.
Sorry but I still cannot find the Module1.OpenCalendar sub.
Where can I see this please ?
rgds and thanks,
Kees
Did you ever get this sorted? I'm getting the same error. "Cannot run the macro ......xlsm !Module1.OpenCalendar"
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.
The Microsoft Date and Time Picker control does not seem compatible with a 64-bit install of Excel 2013.
Rodney,
If you need a 64-bit compatible version, you can find it here:
https://sites.google.com/site/e90e50/calendar-control-class
Also with a pop-up functionality.
Cheers,
Kris
and the FrankensTeam
Thanks for the lesson. I look forward to using the classes on vba.
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...
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.
Jules,
You may use the Date Time Picker control directly on the userform instead of using this.
~VijaySharma
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.
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
Will this still work if you send the spreadsheet to somebody who doesn't have the MSCOMCT2.OCX installed on their computer?
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...
Can any one please tell where to found "syswow64" folder in windows 7
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
[…] 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. […]
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?
Sam,
As it was told above, a 64-bit compatible version could be found here:
https://sites.google.com/site/e90e50/calendar-control-class
Also with a pop-up functionality.
Cheers,
Kris
and The FrankensTeam
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?
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.
@Arturo
Have a read of: http://chandoo.org/wp/2014/04/07/huis-calendar-tool/
I have been using this pop-up calendar for a couple of years. Is there any way to set it up so it opens by default the current month?? I now have to go in every few months and change the 'MonthView1' Properties for the Value to a more current date.
OMG!!
I just re-read this post and I found the answer in like less than 30 seconds!! Thank YOU SOOOOO much Chandoo
Has anyone been able to find a work-around the nonsense of Microsoft not making MSCOMCT2.OCX a default file in Excel? I imagine, for most of us, we are creating files not just for ourselves, but for others.
I am far from a computer genius, but certainly know everything I need to do to get by and make appropriate changes (with help from sites like this).
But, the people I would be making files for would not know how to download and drop files into the appropriate folders. Not to mention know how to run a CMD prompt.
So anyone out there...find a work around? Or maybe another option for Calendar Dates that is embedded already in Excel 2013?
@Mark,
Did you read the comments above?
I hope this solution could help you:
https://sites.google.com/site/e90e50/calendar-control-class
Cheers,
Kris
and The FrankensTeam
Thanks for information.
I made template this topic and I added a date userform to right-click(context) menu.In this way you can add "date" with right-click menu to selected cell.
For this calendar, you do not need to use plug-in, .ocx file etc.
Link : https://netmerkez.wordpress.com/excel/add-right-click-menu
Hi.
The http://activex.microsoft.com/controls/vb6/mscomct2.cab link is not working so the rest of your instruction is useless.
Cheers,
Bruce
Hi,
I have just created a pop-up calendar in my excel sheet. I want to share it with my collegues, how to do that? what has to be installed on their computer and which file should I send to them to make it work? Thank you!
I've tried following this, but it seems that my beginner skills are not up to par. I've got everything working, but it is not clear on how to set up or where to add the 'Insert Date' into the context bar. I've followed all your directions, matched my spreadsheet to yours and read the related articles on setting up a command bar, but I can't seem to get the Right Click feature to work. The only way the macro works is if I actually select to run it. Is there something that I'm missing here?
Hi,
I managed to get the macro working but even after I have deleted this macro file, i still see the function returned when I right-click on the cell, is there anyway this can be removed?
Thanks,
Sachin
How can I avid popup calendar from opening when I sorted or filtered the cells?