Excel: only allow user to enter a date

What VBA would I need so that a user can only enter a date in my textbox in my Excel spreadsheet. (i.e. if the user enters "Hello", the textbox won't allow it, maybe it will delete "Hello" and display a messagebox saying: "Dates only please" etc.)
ouestqueAsked:
Who is Participating?
 
priteshjchauhanConnect With a Mentor Commented:
   With Selection.Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="1/1/1900", Formula2:="=NOW("
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Error"
        .InputMessage = ""
        .ErrorMessage = "Please enter a valid date."
        .ShowInput = True
        .ShowError = True
    End With
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello ouestque,

do you mean a textbox or a cell? A date is a number, so data validation code for a cell only needs to check if the number is greater than 0, like the code below. If you want to limit data entry of future dates, then the previous code will work, if you add a closing parens in the NOW() parameter.

You can also specify that the maximum date is, for example 30 days from today. For this, use

Formula2:="=NOW()+30"

If you want to validate a textbox, the code will need to be completely different. Please clarify.

cheers, teylyn
With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlGreater, Formula1:="1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Wrong data type"
        .InputMessage = ""
        .ErrorMessage = "Enter valid dates only"
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
This code will check a textbox and allow only dates.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
    On Error GoTo Error01
    vvar = DateValue(Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text)
    Exit Sub
Error01:
    Application.EnableEvents = False
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
    Application.EnableEvents = True
    MsgBox "Please enter a valid date"
End Sub

Open in new window

0
 
Rory ArchibaldConnect With a Mentor Commented:
Assuming a userform, then something like this (note though that IsDate is quite expansive in its determination of dates):



Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(TextBox1.Text) > 0 Then
        If Not IsDate(TextBox1.Text) Then
            MsgBox "must enter a date"
            Cancel = True
        End If
    End If
End Sub

Open in new window

0
 
ouestqueAuthor Commented:
Awesome answers thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.