VBA date picker

Often a date needs to be selected in an application and then it is very nice to show a calendar that can be used as a date picker. At the bottom of this page you can download an Excel file that contains a very user-friendly Date Picker that can easily be added to your own applications. The file contains 3 class modules: clDatePickerControl, clDatePickerForm and iDatePickerForm which in fact form the heart of the tool. In addition, this file contains a form frmDatePicker that is otherwise completely empty and where the dimensions are also not important. Filling in the form and setting the dimensions is completely handled by the class modules. For this date picker to work properly the following code needs to be added to the form:

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2024-2025 worksheetsvba.com, all rights reserved
' Date      : 2025-01-10
' Version   : 1.4
' Purpose   : DatePicker
'--------------------------------------------------------------------------------------------------------------------------

Option Explicit

Private oDatePickerForm As iDatePickerForm, oTargetObject As Object, dSelectedDate As Date

Public Property Let SelectedDate(dVal As Date)
    dSelectedDate = dVal
End Property

Public Property Get SelectedDate() As Date
    SelectedDate = dSelectedDate
End Property

Public Property Set TargetObject(oObj As Object)
    Set oTargetObject = oObj
End Property

Private Sub UserForm_Activate()
    Set oDatePickerForm = New clDatePickerForm
    With oDatePickerForm
        Set .DatePickerForm = Me
        Set .TargetObject = oTargetObject
        .ShowWeekNumbers = True
        .ShowColumnTitles = True
        .FirstDayOfWeek = vbMonday
        .DatePickerTitle = "DatePicker"
        .DrawDatePicker
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    oDatePickerForm.TerminateCalendar
    Set oDatePickerForm = Nothing
    Set oTargetObject = Nothing
End Sub

The date picker can then be called as follows:

Private Sub btnFillLabelWithDate_Click()
    Load frmDatePicker
    Set frmDatePicker.TargetObject = LblDate
    frmDatePicker.Show
End Sub

For the TargetObject you can currently choose between a Range, a TextBox or a Label. After choosing a date it will be automatically placed in the TargetObject.

It is also possible to assign the selected date to a variable. This can be done with the following call:

Private Sub btnFillVBAVariableWithDate_Click()
    Dim sSelectedDate As Date
    Load frmDatePicker
    frmDatePicker.Show
    sSelectedDate = frmDatePicker.SelectedDate
    If sSelectedDate <> 0 Then
        Unload frmDatePicker
    End If
End Sub

Settings

The Date Picker contains various options to customize it to your own wishes. There are several 'properties' available for this that can be added to the code of UserForm_Activate. It is important that this is added directly before the line of code with .DrawDatePiacker. The demo file that can be downloaded shows how these options can be used. The Date Picker currently contains the following setting options:

FirstDayOfWeek

Each day of the week can be chosen to be shown first on the Date Picker. If this is not set, the first day according to the system settings will be used.

UseISOWeekNumbers

This setting can be used to specify whether the ISO week number or the US week number should be used for the week numbers. The ISO week number is used by default.

ShowWeekNumbers

This setting can be used to indicate whether week numbers should be displayed on the calendar. By default, this option is off. When ShowWeekNumbers=True then there are additional conditions to show the week number. When UseISOWeekNumbers is True then FirstDayOfWeek should be Monday and when UseISOWeekNumbers is False then FirstDayOfWeek should be Sunday. For all other combinations of UseISOWeekNumbers and FirstDayOfWeek, ShowWeekNumbers has no effect.

ShowColumnTitles

This allows you to make day indications above the columns visible or invisible. By default, column titles are invisible.

DatePickerTitle

The title to be displayed on the Date Picker form can be set with DatePickerTitle. By default it is DatePicker.

DateFormatString

DateFormatString can be used to set how the text value of the selected value should be displayed in a Label or TextBox. This must be a valid format string (e.g. "dd-mm-yyyy"). By default this string is empty and the chosen date is written as Date.

Keyboard controls

The Date Picker can be operated entirely with the keyboard. The following keys can be used:

Tab and arrow right and left

The Tab and left and right arrow keys can be used to navigate from right or left across all elements of the date picker.

PageUp and PageDown

PageUp and PageDown can be used to navigate to the next or previous month.

Space and Enter

The space bar or Enter key can be used to select the active date, after which the form will close. This is similar to clicking on a date with the mouse. When the previous or next month buttons have focus, you can navigate to this month using the space bar or Enter key.

Esc

With the Escape key the Date Picker can be closed without selecting a date.

Demo file

A fully working demo version of the Date Picker can be downloaded below.

There are further updates planned for this tool that will further improve the tool's user flexibility and capabilities, so check this page regularly to see if a new version is available.

Date picker 1.4

Download

Questions / suggestions

Hopefully, this article helped you to use this Date Picker. If you have any questions about this topic or suggestions for improvement, please post a comment below.