InputBox a date

[Otto Moehrbach]

Excel 2002, WinXP
I want the user to respond to an InputBox with a date, like 1/1/05.
However, subsequent checks of this date against another date fail because
the Input date is a string.
I know that I can force the Input 1/1/05 into a date with the DateSerial
function. Is there a quicker, better way? The variable that gets the Input
date is declared a Variant.
Thanks for your help. Otto

 

[Otto Moehrbach]

I was able to do it by placing the value of the variable in a cell, then
setting the variable equal to the contents of that cell. In my particular
scenario that worked OK since I would be putting the variable into that cell
anyway.
But my question still stands. What is the best way to get a date from
an InputBox, either function or method. Thanks. Otto

 

[Dave Peterson]

How about something like:

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(Me.TextBox1.Value) Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please enter a date!"
Cancel = True
End If
End Sub

But if you really want more control, maybe using a calendar control would be
better.

Ron de Bruin has some info at:
http://www.rondebruin.nl/calendar.htm

At the bottom of that page is a link to a free calendar control.

 

[Otto Moehrbach]

Dave
Thanks for your response. However, I am using an IsDate check already
to check that the user did enter a "Date". Looking in Help at the IsDate
function, it states that the IsDate check only checks to see if the value
"can be converted to a date". IOW, it may well be a string and the IsDate
check will still be True. That is the crux of the problem I am having. The
user is entering a "Date" per the IsDate check, but a subsequent query to
compare that "Date" to the same real date comes up False.
Am I out in left field with this? I would appreciate your comments on
this. Thanks again. Otto

 

[Dave Peterson]

You want to convert it to a date, too?

Option Explicit
Sub testme()
Dim myVal As Variant
Dim myDate As Date

myVal = "01/03/05"

If IsDate(myVal) Then
myDate = CDate(myVal)
MsgBox myDate
End If

End Sub

 

[Otto Moehrbach]

Dave
That is exactly what I needed. How do you know all this stuff?? Thanks
for taking the time. Otto

 

[Dave Peterson]

I've read the answers by other posters!

(I don't bother with footnotes anymore, though. <vbg>)

Dave
That is exactly what I needed. How do you know all this stuff?? Thanks
for taking the time. Otto