VBA code to ask user enter the correct date format

[pmchris]

Hi,

Basically, the code asks user to enter the date in format “m/d/yyyy”. If the format is incorrect, it asks the user to enter again until the format is correct. If user clicks the “cancel” button, the macro stops. If the format is correct, then continue to the following procedure (e.g. Range("A1").Value = dt). I declare the variable, use InputBox function and the code for “cancel”, but how can I develop the rest of codes for what I want. Thanks in advance.

Sub save_as_date()

Dim dt As String

dt = InputBox("enter date in format m/y/yyyy")


If dt = "" Then Exit Sub


Range("A1").Value = dt

end sub

 

[Leith Ross]

Hello pmchris,

You don't have to force the user to enter a particular date format. Let the user any valid date format and then convert it to the format you want to display.

Sub save_as_date()


    Dim dt As Variant


AskForDate:
        dt = InputBox("enter date in format m/d/yyyy")


        If dt = "" Then Exit Sub
        If Not IsDate(dt) Then GoTo AskForDate
        If IsDate(dt) Then Range("A1").Value = Format(dt, "m/d/yyyy")


End Sub

 

[pmchris]

Thanks Leith, this is much easier and better than what I initially thought.

Chris