restrict entry in userform textbox

robertvdb

Active Member
Joined
Jan 10, 2021
Messages
334
Office Version
  1. 2016
Platform
  1. Windows
I have a Userform in which there is a textbox, in which a date must be entered.

How can I restrict entries to real dates in the "dd/mm/yyyy" format, and ignore any other/invalid entries ?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I have a Userform in which there is a textbox, in which a date must be entered.

How can I restrict entries to real dates in the "dd/mm/yyyy" format, and ignore any other/invalid entries ?

Hi
don't worry too much about the date format entry made by users - just check if text they have entered is a date & then change it to your required format.

example

VBA Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Const DateFormat As String = "dd/mm/yyyy"
    With Me.TextBox1
        Cancel = Len(.Value) > 0 And Not IsDate(.Value)
        If Not Cancel Then
            .Value = Format(.Value, DateFormat)
        Else
            MsgBox "Please Enter A Valid Date", 48, "Invalid Entry"
            .Value = ""
        End If
    End With
 
End Sub

If user enters a date like 12 jan 2024 code should when they exit the control, change it to your required format.

Do be mindful what comes out of a textbox is text - you can use one of type conversions function to coerce this to required data type when posting to range & apply your required format using numberformat.

Dave
 
Last edited:
Upvote 0
Solution
Thanks Dave, works well.

I made a minor change to your code, in order to avoid blank entries.

Cancel = Len(.Value) >= 0 And Not IsDate(.Value)

Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top