Creating a Time Picker in a UserForm in Excel Using VBA

Features of Our Custom Time Picker

• Hours, Minutes, and AM/PM selection
• Spin buttons to increase/decrease hours and minutes
• Dropdown list for selecting AM or PM
• OK and Cancel buttons to confirm or close the picker
• Time input validation

Step-by-Step Implementation

1. Designing the UserForm

We will create a UserForm named TimePickerForm with the following controls:

2. Writing the VBA Code

Here is the detailed VBA code to handle the Time Picker functionality.

VBA Code for the UserForm:

Option Explicit

' Declare a variable to store the selected time
Public SelectedTime As String
' Initialize the Time Picker when the form loads
Private Sub UserForm_Initialize()
    ' Set default time to 12:00 AM
    txtHours.Text = "12"
    txtMinutes.Text = "00"
    ' Populate the AM/PM dropdown
    cmbAMPM.AddItem "AM"
    cmbAMPM.AddItem "PM"
    cmbAMPM.ListIndex = 0 ' Default to AM
    ' Set spin button properties
    spnHours.Min = 1
    spnHours.Max = 12
    spnHours.Value = 12
    spnMinutes.Min = 0
    spnMinutes.Max = 59
    spnMinutes.Value = 0
End Sub
' Handle Hour Spin Button
Private Sub spnHours_Change()
    txtHours.Text = Format(spnHours.Value, "00")
End Sub
' Handle Minute Spin Button
Private Sub spnMinutes_Change()
    txtMinutes.Text = Format(spnMinutes.Value, "00")
End Sub
' Handle Manual Input in Hours TextBox
Private Sub txtHours_Change()
    Dim h As Integer
    If IsNumeric(txtHours.Text) Then
        h = CInt(txtHours.Text)
        If h >= 1 And h <= 12 Then
            spnHours.Value = h
        Else
            txtHours.Text = "12" ' Reset invalid input
        End If
    Else
        txtHours.Text = "12"
    End If
End Sub
' Handle Manual Input in Minutes TextBox
Private Sub txtMinutes_Change()
    Dim m As Integer
    If IsNumeric(txtMinutes.Text) Then
        m = CInt(txtMinutes.Text)
        If m >= 0 And m <= 59 Then
            spnMinutes.Value = m
        Else
            txtMinutes.Text = "00" ' Reset invalid input
        End If
    Else
        txtMinutes.Text = "00"
    End If
End Sub
' Handle OK Button Click - Store the selected time
Private Sub btnOK_Click()
    SelectedTime = txtHours.Text & ":" & txtMinutes.Text & " " & cmbAMPM.Text
    Me.Hide ' Hide the form instead of unloading it
End Sub
' Handle Cancel Button Click - Close the form without saving
Private Sub btnCancel_Click()
    SelectedTime = "" ' Reset time
    Me.Hide
End Sub

3. Using the Time Picker in Your VBA Code

To display the Time Picker and get the selected time, use the following macro in a module:

Sub ShowTimePicker()
    Dim TimePicker As New TimePickerForm
    ' Show the UserForm
    TimePicker.Show vbModal
    ' Retrieve the selected time
    If TimePicker.SelectedTime <> "" Then
        MsgBox "You selected: " & TimePicker.SelectedTime, vbInformation, "Time Picker"
    Else
        MsgBox "Time selection canceled.", vbExclamation, "Time Picker"
    End If
End Sub

4. How It Works

1. The UserForm_Initialize event sets the default values (12:00 AM).
2. The spin buttons adjust hours (spnHours_Change) and minutes (spnMinutes_Change).
3. Manual input in textboxes is validated (txtHours_Change and txtMinutes_Change).
4. Clicking « OK » saves the selected time and hides the form.
5. Clicking « Cancel » hides the form without saving.
6. The ShowTimePicker macro launches the time picker and displays the selected time.

Enhancements

• You can format the selected time as a 24-hour format.
• You can integrate it with a date picker to get both date and time.
• You can add a preview label that shows the selected time dynamically.

Final Thoughts

This custom Time Picker provides an interactive and user-friendly way to select time values in Excel. Since Excel VBA lacks a built-in Time Picker control, this method offers a flexible alternative that ensures accuracy and ease of use.