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.