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 and versatile Date Picker that can easily be added to your own applications. This Date Picker contains many configuration options and is suitable for both Office 32-bit and 64-bit.

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-04-25
' Version   : 5.2
' Purpose   : DatePicker
'--------------------------------------------------------------------------------------------------------------------------

Option Explicit

Private oDatePickerForm As iDatePickerForm, oTargetObject As Object, dSelectedDate As Date, bDateSelectionRequired As Boolean, sDatePickerTitle As String

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

Public Property Let DateSelectionRequired(ByVal bVal As Boolean)
    bDateSelectionRequired = bVal
End Property

Private Sub UserForm_Activate()
    Set oDatePickerForm = New clDatePickerForm
    With oDatePickerForm
        Set .DatePickerForm = Me
        Set .TargetObject = oTargetObject
        .DateSelectionRequired = bDateSelectionRequired
        .DatePickerTitle = sDatePickerTitle
        .DrawDatePicker
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 0 Or (CloseMode = 0 And bDateSelectionRequired = False) Then
        oDatePickerForm.TerminateCalendar
        Set oDatePickerForm = Nothing
        Set oTargetObject = Nothing
    Else
        MsgBox "It is mandatory to choose a date!", vbInformation, "Closing not allowed"
        Cancel = True
    End If
End Sub

The date picker can then be called as follows:

Private Sub btnFillTextboxWithDate_Click()
    Load frmDatePicker
    With frmDatePicker
        Set .TargetObject = TxtBxDate
        .Show
    End With
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 dSelectedDate As Date
    frmDatePicker.Show
    dSelectedDate = frmDatePicker.SelectedDate
    If dSelectedDate <> 0 Then
        Unload frmDatePicker
    End If
End Sub

General 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.

ShowNotCurrentMonthDays

This can be set whether the last days of the previous month or the first days of the next month should also be grayed out visible in the current month. The default is False. If days from another month are visible, these are also selectable.

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 ISO week number is shown when FirstDayOfWeek is Monday. If FirstDayOfWeek is Sunday then US week number is shown. For other values of FirstDayOfWeek weeknumbers are not displayed and the property ShowWeekNumbers has no effect.

ShowColumnTitles

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

DifferentlyColoredDays

With DifferentlyColoredDays you can configure which days should be displayed with a different blue color instead of the default black color. Setting this property is slightly different than other properties.If days should have a different color, they can be specified here. If there are multiple days, they should be separated by a + sign. It does not matter in which order these days are specified. For example, to give the weekend days a different color, the following call can be used:

DifferentlyColoredDays = dpSaterday + dpSunday

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.

Specific settings

The following specific settings can be specified per call to the calendar.

DateSelectionRequired

If DateSelectionRequired is set to true, then choosing a date is mandatory and the date selector cannot be closed with the closing cross at the top right of the form or with the ESC key. By default, this option is set to false.

DatePickerTitle

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

DateFormatString

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.

Shift + PageUp and PageDown

Shift + PageUp and PageDown can be used to navigate to the next or previous year.

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, unless property DateSelectionRequired is set that choosing a date is mandatory.

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 5.2

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.