Input box - specific date format - error handling

[psnandra]

Hi, I need a macro that will prompt the user to enter a start date in cell “O2” and then a end date in cell “O3” via a message box that will only except the date if entered in the format “dd mmm yyyy”. I think I have the basics below, but I want to add the following error handling:


- If entered in the incorrect format excel shows a runtime error. Instead I’d like a message box saying “retry entering start/end date in correct format”. Then returns to the input box that needs to be entered again. Loops until correct.

- Also, a check that the start date is before the end date would be great too.


Dim start_dt As Date

Dim end_dt As Date

start_dt = Application.InputBox("Enter start date in format: dd mmm yyyy")

end_dt = Application.InputBox("Enter end date in format: dd mmm yyyy")


Range("O2").Select

ActiveCell.Value = start_dt

MsgBox Format(start_dt, "dd mmm yyyy")


Range("O3").Select

ActiveCell.Value = end_dt

MsgBox Format(end_dt, "dd mmm yyyy")


End Sub

 

[Luke M]

Why force the user to input the date in a specific way? A more versatile method would be to let the user input however they want, and then control the output. You've already defined Start_dt and end_dt as dates, so the code will handle any date format the user gives. To get the cell in the proper format, you can format the cells to your desire. Your MsgBoxs already use the Format command to get the format you need.

Would this work for you?

[pre]

Sub Dates()
Dim start_dt As Date
Dim end_dt As Date

On Error Resume Next

Do
start_dt = Application.InputBox("Enter start date")
If start_dt = 0 Then
MsgBox "Retry entering valid start date", vbOKOnly
End If
Loop While start_dt = 0
Do
end_dt = Application.InputBox("Enter end date")
If end_dt = 0 Then
MsgBox "Retry entering valid end date", vbOKOnly
End If
Loop While end_dt = 0
On Error GoTo 0

Range("O2").Value = start_dt
MsgBox Format(start_dt, "dd mmm yyyy")

Range("O3").Value = end_dt
MsgBox Format(end_dt, "dd mmm yyyy")

End Sub

[/pre]

 

[psnandra]

Hi Luke, very new to using VBA, for some reason I thought that the MsgBox Format was the main element in ensuring that the code recognised the input as a date. Understand now that by defining start_dt and end_dt as dates that the code would handle any input as such. Your suggestion works perfectly, just what I needed. Thank you. Pav

 

[Luke M]

You're very welcome, glad I could help. =)