Prev: check if value exists in pivot table
Next: Control Z-Order of drop lines in an Excel 2007 chart?
From: RustyOnVBA on 1 Apr 2010 19:23
Is there any way to ensure that a user inputs a valid dd/mm/yyyy date into an
input box, the current code is DateInput = InputBox("Please input date ")

Also the ideal the ideal solution would be to use the calendar function but
for the life of me i cannot remember for the life of me what to call for this.

I would also like to know validation on inputboxes in general but thats less
time critical at the moment

Any help would be greatly appreciated
From: OssieMac on 1 Apr 2010 21:34
Hi,

Firstly note that InputBox and Application.InputBox are different. See help
for differences.

The following code will validate a date. Using IsDate on its own is
unreliable. For instance entering IsDate("32/4/10") can return True and
DateValue("32/4/10") converts it to 10 Apr 1932.

The following code splits the enty into its 3 components separated by the
slashes and validates each component. Then the use of IsDate is really only
testing if too many days have been entered for the particular month.

There is no justification for forcing 2 digit entry of the day and month.
The code will validate either two digit or one digit entries. However, the
code will only validate a 4 digit year.

I have tested the code but if you find any conditions where it accepts an
invalid date then please get back to me.

Also, I have included code if the user Cancels to they can abort if necessary.

The If tests of the date components could be combined into one statement but
I did it in a way that is easy to follow the logic.

Sub InputBoxExample()

Dim Response As Variant
Dim dateEntry As Date
Dim strPrompt As String
Dim x

strPrompt = "Enter date as d/m/yyyy."

Do

Response = Application.InputBox _
(Prompt:=strPrompt, _
Title:="Date entry.", _
Type:=2)

If Response = False Then
MsgBox "User cancelled." & vbLf & _
"Processing terminated."
Exit Sub
End If

'x is an array to hold
'elements of the date that
'are separated by "/"
x = Split(Response, "/", -1)

If UBound(x) <> 2 Then
'Must have 3 elements (0 to 2)
GoTo InvalidDate
End If

'Test for day between 1 and 31
If x(0) < 1 Or x(0) > 31 Then
'Invalid day in date entry
GoTo InvalidDate
End If

'Test for month between 1 and 12
If x(1) < 1 Or x(1) > 12 Then
'Invalid month in date entry
GoTo InvalidDate
End If

'Test for year within specified range
'Edit 2000 and 2020 to required valid years
If x(2) < 2000 Or x(2) > 2020 Then
'Invalid year in date entry
GoTo InvalidDate
End If

'An invalid for IsDate indicate incorrect
'number of days for the particular month.
If IsDate(Response) Then
'Assign string date to date variable
dateEntry = DateValue(Response)

'MsgBox for testing only
MsgBox "Date entry is: " & _
Format(dateEntry, "dd mmm yyyy")

Exit Do 'No further testing required

Else
strPrompt = "Error! Invalid date entry." & _
vbLf & "Could be too many days in month." & _
vbLf & "Enter as d/m/yyyy"
End If

'If gets to here then skip the InvalidDate
GoTo PastInvalidDate

InvalidDate:
strPrompt = "Error! Invalid date entry." _
& vbLf & "Enter as d/m/yyyy"

PastInvalidDate:

Loop

End Sub

--
Regards,

OssieMac

From: JLGWhiz on 1 Apr 2010 21:40
DateInput = InputBox("Please input date ")
DateInput = Format(DateInput, "dd/mm/yyyy")



"RustyOnVBA" <RustyOnVBA(a)discussions.microsoft.com> wrote in message
news:EB4A095D-836D-463C-8D60-29B327DE0C7E(a)microsoft.com...
> Is there any way to ensure that a user inputs a valid dd/mm/yyyy date into
> an
> input box, the current code is DateInput = InputBox("Please input date ")
>
> Also the ideal the ideal solution would be to use the calendar function
> but
> for the life of me i cannot remember for the life of me what to call for
> this.
>
> I would also like to know validation on inputboxes in general but thats
> less
> time critical at the moment
>
> Any help would be greatly appreciated


From: ozgrid.com on 1 Apr 2010 22:21
Something like;

http://www.ozgrid.com/VBA/inputbox.htm

Sub InputDate()
Dim DateInput As String
DateInput = InputBox("Please input date ")
If Not IsDate(DateInput) Then
MsgBox "Non valid date"
Run "InputDate"
ElseIf DateInput = vbNullString Then
Exit Sub
Else
'Valid date code
End If
End Sub


--
Regards
Dave Hawley
www.ozgrid.com
"RustyOnVBA" <RustyOnVBA(a)discussions.microsoft.com> wrote in message
news:EB4A095D-836D-463C-8D60-29B327DE0C7E(a)microsoft.com...
> Is there any way to ensure that a user inputs a valid dd/mm/yyyy date into
> an
> input box, the current code is DateInput = InputBox("Please input date ")
>
> Also the ideal the ideal solution would be to use the calendar function
> but
> for the life of me i cannot remember for the life of me what to call for
> this.
>
> I would also like to know validation on inputboxes in general but thats
> less
> time critical at the moment
>
> Any help would be greatly appreciated

From: arjen van der wal on 2 Apr 2010 00:36

Hi Rusty(?)

I think you had the right idea when you mentined using the calendar control.
Then you can just skip the validation business. This control is not on the
defauly Excel menu, but in Excel 2007 if you're on the developer tab, click
on Insert and then click on the lower right to expand your Active-X controls
options. What you're looking for is Calendar Control 12.0 if you're adding
the control to a worksheet. Then the code is fairly simple (this is in a
general module):


Sub DisplayDate()

Dim MyDate As Date

MyDate = Sheet1.Calendar1.Value

MsgBox (Format(MyDate, "mmm/dd/yy"))


End Sub

If you want to add the control to a user form, it's not on the controls
toolbox by default, but you can add it pretty easily. In Visual Basic Editor
go to Tool > Additional Controls and then look for Calendar Control 12.0
after which it should appear on your controls toolbox and you can add it to
your form.

If you're using Excel 2003 I believe it will be Calendar Control 8.0.


 |  Next  |  Last
Pages: 1 2
Prev: check if value exists in pivot table
Next: Control Z-Order of drop lines in an Excel 2007 chart?