InputBox

[Guest]

Using the InputBox (either Excel's method or VBA's function), I'd like the
user to specify a date. But how can I tell if the user has clicked cancel?

Second question: the Excel InputBox allows me to specify the data type.
That's good. But it isn't quite narrow enough (merely 'number' rather than
'date'). Is there a way I can tighten up the data type to 'date'?

Art

 

[Harald Staff]

Hi Art

Try

Sub test()
Dim S As String
Dim Dt As Date
Do
S = InputBox("Enter a date:", "Art sez:", S)
If S = "" Then Exit Sub
On Error Resume Next
Dt = DateValue(S)
Loop Until Year(Dt) > 1995
MsgBox "You wrote " & Format$(Dt, "dddd mmmm dd. yyyy")
End Sub

It allows all common date formats, e.g. jun 1 and 12/03/04. No specified
year means "this year".

HTH. Best wishes Harald

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim myDate As Variant
myDate = InputBox(Prompt:="enter a date")
If Trim(myDate) = "" Then
Exit Sub 'user hit cancel
End If

If IsDate(myDate) Then
myDate = CDate(myDate)
Else
MsgBox "not a date"
Exit Sub
End If
End Sub

You might want to take a look at Ron de Bruin's site for some tips/code/free
calendar control:
http://www.rondebruin.nl/calendar.htm

If the user enters 02/03/04, what date do you expect back. (The calendar
control may make it easier to decipher.)