loveguy1977
01-01-2010, 06:14 AM
Hi dears,
I'm having inputbox macro to enter date. I need to force user to enter the date in the format of DD/MM/YYYY only. Actually now if the the user enter "1", the macro will enter the date to cell as 1/1/1900. If "22" then will fill 22/1/1900 into the cell and so on.
I hope you help me to force user to enter a date in the format of DD/MM/YYYY. Note that I just want date mean no letter accepiable.
Macro is as follow or see attached file
Sub Date_Format_DD_MM_YYYY()
Dim UserDate As Variant
Retry:
UserDate = InputBox(vbNewLine & "Enter the date as Day/Month/Year", _
"Admin Asks You To...", "DD/MM/YYYY")
On Error Resume Next
UserDate = CDate(UserDate)
If Err.Number <> 0 Then
UserDate = MsgBox("Your entry is not a valid date. ", _
vbRetryCancel + vbExclamation, " OOPS")
If UserDate = vbCancel Then
Call Date_Format_DD_MM_YYYY
Else
Err.Clear
GoTo Retry
End If
End If
With ActiveSheet.Range("Addrow").Offset(-1, 5)
.NumberFormat = "DD/MM/YYYY"
.HorizontalAlignment = xlHAlignCenter
.Value = UserDate
End With
End Sub
I'm having inputbox macro to enter date. I need to force user to enter the date in the format of DD/MM/YYYY only. Actually now if the the user enter "1", the macro will enter the date to cell as 1/1/1900. If "22" then will fill 22/1/1900 into the cell and so on.
I hope you help me to force user to enter a date in the format of DD/MM/YYYY. Note that I just want date mean no letter accepiable.
Macro is as follow or see attached file
Sub Date_Format_DD_MM_YYYY()
Dim UserDate As Variant
Retry:
UserDate = InputBox(vbNewLine & "Enter the date as Day/Month/Year", _
"Admin Asks You To...", "DD/MM/YYYY")
On Error Resume Next
UserDate = CDate(UserDate)
If Err.Number <> 0 Then
UserDate = MsgBox("Your entry is not a valid date. ", _
vbRetryCancel + vbExclamation, " OOPS")
If UserDate = vbCancel Then
Call Date_Format_DD_MM_YYYY
Else
Err.Clear
GoTo Retry
End If
End If
With ActiveSheet.Range("Addrow").Offset(-1, 5)
.NumberFormat = "DD/MM/YYYY"
.HorizontalAlignment = xlHAlignCenter
.Value = UserDate
End With
End Sub