The VBA editor does not have a calendar control available to it by default – however it is possible to use the MS Access calendar control. This tutorial is on how to set up a calendar control that can be accessed via a UserForm, or inline from a spreadsheet.
Download the following for an example of how to use the control either to populate a cell on a worksheet, or a control on a form.
1. Setting up a reference to the MSCAL.ocx calendar control.
You will need to set up a reference to the MSCAL.ocx file. Open the “References” dialog (Tools > References in the VBA editor).
Click “Browse” and select the MSCAL.OCX file (this should be located in the root directory of your Microsoft Office installation.
Click “Open”. This will now appear in the “Available References” window as Microsoft Calendar Control xx.x. Make sure the checkbox to the left is enabled.
2. Copy the Userform and Class modules to the workbook.
Copy the “PopupCalendar” class module and the “frmPopupCalendar” module into the target workbook (you can do this by dragging them to the target book in the VBA editor).
3. Add subroutines
Add the following function into a module – this is a will display the form, and return a date object.
Function showCalendar() As Date
Dim pcPopup As PopupCalendar
Dim dtDate As Date
‘create a new PopupCalendar object
Set pcPopup = New PopupCalendar
‘sets the date as today
‘This will show the PopupCalendar as a modal form, meaning that
‘execution of the function will not continue until the form is closed.
‘As the form will now be closed, we can access the date that was selected
dtDate = pcPopup.calDate
Set pcPopup = Nothing
‘return the date
showCalendar = dtDate
4. Add Controls and Event Handlers
Add a Command Button control to the spreadsheet (use the Control Toolbox, accessed via the ‘View > Toolbars > Control Toolbox’ menu item.
Right-click on the control and select the “View Code” popup menu item.
This will take you to the code for the Click event of the command button object. Enter the following code:
Private Sub CommandButton1_Click()
‘Make sure the text before the underscore is the same as the name of your Command Button otherwise the event will not fire.
Dim dtDate As Date
‘Calls the showCalendar function we added before
dtDate = showCalendar()
If dtDate <> 0 Then
‘Note this will be the range that is populated with the date
‘The format statement is used to format the date returned (default is mm/dd/yyyy)
Me.Range(“B6”).Text = Format(dtDate, “dd/mm/yyyy”)
‘As will this
Me.Range(“B6”).Text = vbNullString
That’s it. You can download the example Excel file here: PopupCalendar.xls