Create a Date selector in an Excel UserForm using VBA

We will use three ComboBox controls (for day, month, and year) to allow the user to select a date, and a Label to display the selected date.

Steps:

• Create a UserForm with three ComboBoxes and a Label to display the selected date.
• Populate the ComboBoxes with days, months, and years.
• Add code to display the selected date in a Label when the user selects a day, month, and year.

Complete VBA Code:

1. Creating the Form:

First, create a UserForm with:

• 3 ComboBox controls (for Day, Month, and Year).
• 1 Label control to display the selected date.

2. VBA Code in the UserForm Module:

' In the UserForm module
Private Sub UserForm_Initialize()
    ' Populate the Day ComboBox (1 to 31)
    Dim i As Integer
    For i = 1 To 31
        ComboBoxDay.AddItem i
    Next i   
    ' Populate the Month ComboBox (January to December)
    ComboBoxMonth.AddItem "January"
    ComboBoxMonth.AddItem "February"
    ComboBoxMonth.AddItem "March"
    ComboBoxMonth.AddItem "April"
    ComboBoxMonth.AddItem "May"
    ComboBoxMonth.AddItem "June"
    ComboBoxMonth.AddItem "July"
    ComboBoxMonth.AddItem "August"
    ComboBoxMonth.AddItem "September"
    ComboBoxMonth.AddItem "October"
    ComboBoxMonth.AddItem "November"
    ComboBoxMonth.AddItem "December"
    ' Populate the Year ComboBox (for example, from 2000 to 2024)
    Dim year As Integer
    For year = 2000 To 2024
        ComboBoxYear.AddItem year
    Next year
End Sub
Private Sub ComboBoxDay_Change()
    ' Update the displayed date whenever a day is selected
    DisplayDate
End Sub
Private Sub ComboBoxMonth_Change()
    ' Update the displayed date whenever a month is selected
    DisplayDate
End Sub
Private Sub ComboBoxYear_Change()
    ' Update the displayed date whenever a year is selected
    DisplayDate
End Sub
Private Sub DisplayDate()
    ' Check if all ComboBoxes have a selected value
    If ComboBoxDay.ListIndex <> -1 And ComboBoxMonth.ListIndex <> -1 And ComboBoxYear.ListIndex <> -1 Then
        ' Display the selected date in the Label
        LabelDate.Caption = ComboBoxDay.Value & " " & ComboBoxMonth.Value & " " & ComboBoxYear.Value
    End If
End Sub

Explanation:

• UserForm_Initialize:
  • This procedure is triggered when the form is initialized. It fills the three ComboBox controls with the days, months, and years.
  • For the days, it populates the ComboBox with values from 1 to 31.
  • For the months, it populates the ComboBox with the month names from January to December.
  • For the years, it populates the ComboBox with a range of years (from 2000 to 2024 in this case).
• ComboBoxDay_Change, ComboBoxMonth_Change, ComboBoxYear_Change:
  • These procedures are triggered when a user makes a selection in one of the ComboBox controls (day, month, or year).
  • Each time the user selects a day, month, or year, the DisplayDate function is called to update the displayed date in the Label.
• DisplayDate:
  • This procedure checks if all three ComboBoxes have a selected value (by checking the selected index of each ComboBox).
  • If all selections are valid, it displays the selected date in the Label control in the format « Day Month Year ».

User Interface:

• When the user opens the form, they see three ComboBox controls (for day, month, and year) along with a Label that will display the selected date.
• After the user selects a day, month, and year, the selected date is displayed in the Label (for example: « 15 February 2024 » if the user selects day 15, month February, and year 2024).

Example:

If the user selects:

• Day: 15
• Month: February
• Year: 2024

The Label will display:
« 15 February 2024 ».

Additional Suggestions:

• You can add further functionality, such as validating the date (for example, checking that February doesn’t have more than 29 days depending on the year).
• You can add buttons to validate or cancel the selection if needed.

Conclusion:

This code allows you to create a simple date selector using ComboBox controls in an Excel UserForm with VBA. You can customize it further for more specific needs, such as formatting the date or saving it to a cell in the workbook.