Inputbox force use to ude formatt dd/mm/yyyy

[howard]

I have a macro where the user must inpput a date. I would like this amended so that the date must be entrred as dd/mm/yyy. If not message must appear "Please enter date correctly using dd/mm/yyyy

 Sub Date_Input()
With Sheets("Statement")

Dim i As Variant
 i = InputBox("Please Enter the Statement Month End for e.g. 31/10/2022")

.Range("AA1").Value = i
.Range("AA2").FormulaR1C1 = "=DATEVALUE(R[-1]C)"
    .Range("AA2").Copy
    .Range("AA1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Range("AA2").ClearContents
    
End With

End Sub

 

[Legacy 143009]

  If i <>  Format(DateValue(i), "dd/mm/yyyy") Then
    MsgBox "Format must be 'dd/mm/yyyy'"     
  Else     
    .Range("AA1").Value = i
  End If

This may work for you. This morning I was chatting with a colleague about exactly the same issue. How difficult it is, to validate a date according to user's local settings. It needs fiddling around a bit using CDate(), DateValue() and Format() methods.

 

[HongRu]

Hope this help.

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

 

[howard]

  If i <>  Format(DateValue(i), "dd/mm/yyyy") Then
    MsgBox "Format must be 'dd/mm/yyyy'"    
  Else    
    .Range("AA1").Value = i
  End If

This may work for you. This morning I was chatting with a colleague about exactly the same issue. How difficult it is, to validate a date according to user's local settings. It needs fiddling around a bit using CDate(), DateValue() and Format() methods.

Many thanks for the help

 

[howard]

Hope this help.

Use This Free Excel Date Picker to Enter Dates on Worksheet

Use this Excel Date Picker to select dates for a worksheet cell, or create a quick month or year calendar to sheet. Code is unlocked, to see how it works.

www.contextures.com

Thanks for the link to the date Picker