PDA

View Full Version : Inputbox to force users enter date as DD/MM/YYYY ONLY



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

GTO
01-01-2010, 06:32 AM
Greetings,

I did not look at the attachment yet, but based upon your question and code posted - have you given consideration to a simple userform?

Mark

loveguy1977
01-01-2010, 06:37 AM
Greetings,

I did not look at the attachment yet, but based upon your question and code posted - have you given consideration to a simple userform?

Mark

No because i'm not good in userform

GTO
01-01-2010, 07:53 AM
In a test copy of your wb try:


Sub Date_Format_DD_MM_YYYY()
Dim UserDate As Date
Dim strDateString As String

Retry:
strDateString = InputBox("MSG", "Admin..", Format(Date, "dd/mm/yyyy"))

'// If user cancels, bail//
If strDateString = vbNullString Then Exit Sub

If strDateString Like "##/##/####" Then
If Not (CLng(Left(strDateString, 2)) > 0 _
And CLng(Left(strDateString, 2)) < 32) _
Or Not (CLng(Mid(strDateString, 4, 2)) > 0 _
And CLng(Mid(strDateString, 4, 2)) < 13) _
Or Not (CLng(Right(strDateString, 4)) > 1960 _
And CLng(Right(strDateString, 4)) < 2020) Then

Call GetMsg
GoTo Retry
End If
Else
Call GetMsg
GoTo Retry
End If

UserDate = DateSerial(CLng(Right(strDateString, 4)), _
CLng(Mid(strDateString, 4, 2)), _
CLng(Left(strDateString, 2)))

With ActiveSheet.Range("Addrow").Offset(-1, 5)
.NumberFormat = "DD/MM/YYYY"
.HorizontalAlignment = xlHAlignCenter
.Value = UserDate
End With
End Sub

Hope that helps,

Mark

Paul_Hossler
01-01-2010, 09:07 AM
Another way to ensure that you're getting a date, without forcing the user to a particular input format, is to check the input just to see if it's a valid date, regardless of format entered (1/2/2010, May 5, 2001, 1/2, etc)



Sub DatesIn()
Dim dtInput As Variant
Dim bInputOK As Boolean

bInputOK = False
While Not bInputOK
dtInput = InputBox("Enter A Date", "Dates Only")

If IsEmpty(dtInput) Or IsDate(dtInput) Then
bInputOK = True
Else
Call MsgBox("Need a real date", vbCritical + vbOKOnly, "Dates Only")
End If
Wend

If Not IsEmpty(dtInput) Then
'demo only
Worksheets("Sheet1").Cells(12, 12).NumberFormat = "mm/dd/yyyy"
Worksheets("Sheet1").Cells(12, 12).Value = dtInput
End If
End Sub


Paul

lucas
01-01-2010, 10:33 AM
Loveguy,
Please read this before posting again:

Why crossposting without providing a link is inconsiderate to those who are here to help you. (http://www.excelguru.ca/node/7)