Software

General discussion

Gravatar
0 Votes
Locked

VBA DATE FORMAT VALIDATION

By david_j_will ·
I have a macro that has a number of application input boxes requesting a user to key data into specific fields in a workbook. There are about 6 that request dates and I want to validate the format of the date the user is inputting to stop them keying in 10/12/200 which Excels classifies as a validate date in the year 200. I've tried using the "On Error Goto Line" but it doesn't work.

Line1:
' Prompt for adjusted service date
On Error GoTo Line87
Sheets("LSL PAY IN LIEU").Select
Range("E10").Select
NUMBERTEXT = Application.InputBox( _
prompt:="ENTER ADJUSTED SERVICE DATE ", _
Title:="Employee's Adjusted Service Date")
Selection.NumberFormat = "d/mm/yyyy"
If NUMBERTEXT = "False" Then GoTo Line84
If (Len(NUMBERTEXT) <> 0) Then
NUMBERDATE = DateValue(NUMBERTEXT)
ActiveCell.FormulaR1C1 = NUMBERDATE
Else: ActiveCell.FormulaR1C1 = ""
End If

This is the script for one of the input boxes.

Line 84 is a message box to tell the user that they have exited the spreadsheet.

Line 87 tells the user that the format of the date is incorrect.

I want to use these lines for all the input boxes when the date format is incorrect (3 digit year code) and go back to the line with the error so the user can erkey in the date.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by chitosunday In reply to VBA DATE FORMAT VALIDATI ...

Do a similar code like this
Sub inputagain()
On Error GoTo line87:
NUMBERTEXT = Application.InputBox( _
prompt:="ENTER ADJUSTED SERVICE DATE ", _
Title:="Employee's Adjusted Service Date")
Selection.NumberFormat = "d/mm/yyyy"
If (Len(NUMBERTEXT) <> 0) Then
NUMBERDATE = DateValue(NUMBERTEXT)
ActiveCell.FormulaR1C1 = NUMBERDATE
Else: ActiveCell.FormulaR1C1 = ""
End If
Exit Sub
line87:
inputagain

End Sub

gravatar
Collapse -

by david_j_will In reply to

Poster rated this answer.

gravatar
Collapse -

by david_j_will In reply to VBA DATE FORMAT VALIDATI ...

This question was closed by the author

Back to Software Forum

Start or search

Related Discussions

Related Forums