VBA Date Validation?

[jsabo]

Hello,


Trying to user-input-error-proof a new macro w/ UI that I'm building in Word. As far as I can tell, I am unable to use an input mask... So i have the default value of the field as "DD-MMM-YYYY" to clearly hint for the user. But, I would still like to have validation that passes/fails if the user inputs correctly/incorrectly.


Here's what I'm trying so far, just to test in a separate file:



Dim oDate As Date
oDate = DateBox.Value
If Not oDate Like "##[-]???[-]####" Then
MsgBox "Please enter all dates in the following format: DD-MMM-YYYY"
Exit Sub
Else
MsgBox oDate
End If


Not really my first preferred go-to, but I think it could be sufficient. This in theory would validate that a date is entered like "05-Mar-2014". Any other suggestions or am I close to makine this work? Cheers.

[macropod]

You could just use:
DateBox.Value = Format(CDate(DateBox.Value), "DD-MMM-YYYY")
That way, it really doesn't matter what input format is used.

[jsabo]

So, here is where I am now:


'date validation
If Not IsDate(ITTIssueDate.Value) Then
MsgBox "Please enter all dates in the following format: DD-MMM-YYYY"
Exit Sub
Else
IID = Format(ITTIssueDate.Value, "DD-MMM-YYYY")
End If
If Not IsDate(TenderDueDate.Value) Then
MsgBox "Please enter all dates in the following format: DD-MMM-YYYY"
Exit Sub
Else
TDD = Format(TenderDueDate.Value, "DD-MMM-YYYY")
End If


Fnd = "[insert ITT number]": Rep = ITTNumber.Value
Fnd2 = "[insert ITT title]": Rep2 = ITTTitle.Value
Fnd3 = "[insert date]": Rep3 = IID
Fnd4 = "(insert date)": Rep4 = TDD
Fnd5 = "(insert Subcontract Formation Specialist name)": Rep5 = FormationSpecialist.Value


First the code looks to see if it's actually a date that's input. If not, it forces the the user to put in a date. Then I try to assign the value of the text box to a certain variable and dictate the format "DD-MMM-YYYY". But, whenever the date is spit out it is not DD-MMM-YYYY, rather M/D/YY. Any ideas?

Got it working here, this makes more sense and is more efficient too:



If Not IsDate(ITTIssueDate.Value) Then
MsgBox "Please enter all dates in the following format: DD-MMM-YYYY"
Exit Sub
End If


If Not IsDate(TenderDueDate.Value) Then
MsgBox "Please enter all dates in the following format: DD-MMM-YYYY"
Exit Sub
End If


Fnd = "[insert ITT number]": Rep = ITTNumber.Value
Fnd2 = "[insert ITT title]": Rep2 = ITTTitle.Value
Fnd3 = "[insert date]": Rep3 = Format(ITTIssueDate.Value, "DD-MMM-YYYY")
Fnd4 = "(insert date)": Rep4 = Format(TenderDueDate.Value, "DD-MMM-YYYY")
Fnd5 = "(insert Subcontract Formation Specialist name)": Rep5 = FormationSpecialist.Value

[macropod]

You really haven't paid much attention to what I posted...

If Not IsDate(ITTIssueDate.Value) Then
MsgBox "Please a date!"
Exit Sub
Else
ITTIssueDate.Value = Format(CDate(ITTIssueDate.Value), "DD-MMM-YYYY")
End If

[jsabo]

Okay, have adopted your advice. Thanks.