Date picker in user form?

[TheWennerWoman]

Hello,

I have a userform. When the user clicks into one of the fields, I'd like a calendar to pop up so that they can pick a date (mainly to avoid dirty data being entered onto the form).

Is this possible? I have Googled and it appears that office 365 doesn't have the functionality (although apparently Office 2010 did?!)

Many thanks for reading.

 

[dmt32]

Hi,

There is no consistency in the availability of the DatePicker in all Excel versions so personally, I would avoid using it.

There are plenty of “Home Brew” userform versions you can download for free, some use classes others are all VBA in the Userform - any may do what you want.

A more straightforward approach you could consider is create a simple function to test that user has entered a date (in any format) & then when exit the textbox, apply your preferred format

Place in either Standard Module or Forms Code Page

Function IsNotValidDate(ByVal objTextBox As Object, Optional ByVal DateFormat As String = "dd/mm/yyyy") As Boolean
    With objTextBox
        If Len(.Value) > 0 Then
            IsNotValidDate = Not IsDate(.Value)
            If IsNotValidDate Then
                .Value = ""
                MsgBox "Please enter a valid date.", 48, "Invalid Date Entry"
            Else
                'format date
                .Value = Format(DateValue(.Value), DateFormat)
            End If
        End If
    End With
End Function

To call from your textbox

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = IsNotValidDate(Me.TextBox1)
End Sub

User should be able to enter a date in any format (25 May 2022) & function should apply your specified format or default format if omitted,

Hope Helpful

Dave

 

[TheWennerWoman]

Thank you, works a treat

 

[dmt32]

Thank you, works a treat

most welcome glad suggestion does what you want & appreciate feedback

Dave

 

[vbaSimonC]

Hi dmt32,
Great post, thank you for the code, this has helped me loads.

In my Userform I also want to get the user to capture the time, and keep it in format "hh:mm".

How do I do this? Can I manipulate the code and change "Date" to "Time"?
Thanks
S