Pop up Calendar Add In Using Excel VBA

How to create a Pop-up Add-In using Excel VBA in MS Excel 2010 that will help us to enter dates into our Excel worksheet cells quickly and easily.
We embed a calendar in a user-form. Behind the calendar we place a command button so that the user can press ‘ESC’ from the keyboard to close the pop-up calendar.
We code the command button:

Private Sub CommandButton1_Click()
Unload Me
End Sub

Next we code the calendar so that data can be transferred on click and the form is closed:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
On Error Resume Next
ActiveCell.Value = DateClicked
Unload Me
End Sub

Next we synchronize the data between the activecell and the calendar on initialization of the userform named as frmCalendar.

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Me.MonthView1.Value = ActiveCell
Me.MonthView1.Value = Now
End If
End Sub

If the activecell contains a date our calendar displays the date of the activecell otherwise it displays today’s date.

Now when and how does the user insert a date into a worksheet cell? The user will have a header date under which he may like to enter a date quickly and easily. Normally we use the right-mouse button to display a context menu and perform our actions. So we’ll add a new item to the context menu called ‘insert date’ using the following VBA code:

Private Sub Workbook_Open()
On Error Resume Next
Dim NewControl As CommandBarControl
Application.OnKey “+^(C)”, “Module1.OpenCalendar”
Application.CommandBars(“Cell”).Controls(“Insert Date”).Delete
Set NewControl = Application.CommandBars(“Cell”).Controls.Add
‘we can change the position of the menu item by changing our code as shown below
‘Set NewControl = Application.CommandBars(“Cell”).Controls.Add(, , , before:=3)
With NewControl
.Caption = “Insert Date”
.OnAction = “Module1.OpenCalendar”
‘Read more about the object variable BeginGroup here: http://msdn.microsoft.com/EN-US/library/office/ff861759.aspx
.BeginGroup = True
End With

End Sub

In the code above we notice that a shortcut key combination (Shift+Ctrl+C) has also been provided to open the calendar. Also we need to create a Module1 with the name OpenCalendar. The code for the module1 is given below:

Sub OpenCalendar()
End Sub

Now once the user has selected and inserted the required date the form closes but many of the commands will remain in the memory. So before the workbook closes we would like to clear the computer memory of such commands. Good programming practice demands this. Here is the VBA code to do exactly this memory cleanup:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey “+^(C)”
Application.CommandBars(“Cell”).Controls(“Insert Date”).Delete
End Sub

Finally we save the file as a macro-enabled workbook.

We are now ready to create our pop-up calendar add-in.

With a few modifications you should be able to do this also in Excel 2007.
Watch the Excel VBA training video below for all the details:

Further reading:
Customizing Context Menus in All Versions of Microsoft Excel
A Pop-up Calendar for Excel

Leave a Reply

Your email address will not be published. Required fields are marked *