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.
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
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
VBA DATE FORMAT VALIDATION
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.