thanks,,now I know ^_^
Pesach Shelnitz wrote:
Hi Matt,An InputBox always returns a String, even if the string represents a
17-Aug-09
Hi Matt,
An InputBox always returns a String, even if the string represents a date.
If the user cancels the InputBox, the string returned by InputBox is an empty
string ("").
If the user enters a correctly formatted date, you can use the CDate
function to convert the string to a Date variable. The following macro is a
modified version of your macro that converts the string returned by InputBox
to a Date and then formats the date differently and displays the result.
Sub DateDemo()
Dim StringDate As String
Dim SchedDate As Date
msg = "Enter Scheduled date (for ex: 4/1/09)"
Title = "Scheduled Date"
StringDate = InputBox(msg, Title)
If StringDate = "" Then
Exit Sub
End If
SchedDate = CDate(StringDate)
MsgBox Format(SchedDate, "dddd, MMM d yyyy")
End Sub
--
Hope this helps,
Pesach Shelnitz
:
Previous Posts In This Thread:
Hi Matt,An InputBox always returns a String, even if the string represents a
Hi Matt,
An InputBox always returns a String, even if the string represents a date.
If the user cancels the InputBox, the string returned by InputBox is an empty
string ("").
If the user enters a correctly formatted date, you can use the CDate
function to convert the string to a Date variable. The following macro is a
modified version of your macro that converts the string returned by InputBox
to a Date and then formats the date differently and displays the result.
Sub DateDemo()
Dim StringDate As String
Dim SchedDate As Date
msg = "Enter Scheduled date (for ex: 4/1/09)"
Title = "Scheduled Date"
StringDate = InputBox(msg, Title)
If StringDate = "" Then
Exit Sub
End If
SchedDate = CDate(StringDate)
MsgBox Format(SchedDate, "dddd, MMM d yyyy")
End Sub
--
Hope this helps,
Pesach Shelnitz
:
Hi Matt,It is possible to bend things a bit from what I described because VB
Hi Matt,
It is possible to bend things a bit from what I described because VB and VBA
both try to work as best as they can with the code that users write. Since
you declared SchedDate as a Date variable, VBA automatically converts the
string returned by InputBox to the Date type if possible. If you move the
line containing CDate in my macro to be before the If statement and enter 0
as the string, the date December 30, 1899 will appear in the message box, and
if you would add the time formatting in the call to Format, the time 12:00:00
AM would also appear in the message box. This is essentially what your macro
does for 0 in the call to InputBox. In your If statement you try to compare
your Date variable to 0. This works fine if the user enters 0, but cancel
returns an empty string, which VBA cannot convert to a Date value. At this
point, VBA raises the type mismatch error.
You might find these links to the reference pages for the InputBox, CDate,
and Format functions in MSDN helpful.
InputBox -
http://msdn.microsoft.com/en-us/library/6z0ak68w.aspx
CDate -
http://msdn.microsoft.com/en-us/library/s2dy91zy.aspx
Format function -
http://msdn.microsoft.com/en-us/library/59bz1f0h.aspx
Pesach
:
Hi Matt,If a user might not enter a valid date, your code needs code to check
Hi Matt,
If a user might not enter a valid date, your code needs code to check for
the error and exit gracefully. The following modification of my previous
macro includes such error handling.
Sub DateDemo()
Const Error_TypeMismatch = 13
Dim StringDate As String
Dim SchedDate As Date
msg = "Enter Scheduled date (for ex: 4/1/09)"
Title = "Scheduled Date"
StringDate = InputBox(msg, Title)
If StringDate = "" Then
Exit Sub
End If
On Error Resume Next
SchedDate = CDate(StringDate)
If Err.Number = Error_TypeMismatch Then
MsgBox "A valid date was not entered.", _
vbCritical Or vbOKOnly, "Type Mismatch"
Exit Sub
End If
On Error GoTo 0
MsgBox Format(SchedDate, "dddd, MMM d yyyy")
End Sub
Pesach
:
Date Variable - Input Box - Click Cancel - Type Mismatch Error
Here is the code that is giving me a problem...
Dim SchedDate As Date
msg = "Enter Scheduled date (for ex: 4/1/09)"
Title = "Scheduled Date"
SchedDate = InputBox(msg, Title) '***--This is the line that throws
the error when clicking cancel
If StrPtr(SchedDate) = 0 Then '--If user clicks cancel
Exit Sub
End If
[More validation code follows]
The input box asks for a date. It should assign the input to the date
data type variable SchedDate. However, if you click cancel at the
input box, it gives a runtime error 13: type mismatch. When I debug
and hover over the [SchedDate], it shows a value of 12:00:00 AM.
I cannot even get to the code that checks if a user clicks cancel.
The only thing I can think of is to declare SchedDate as a string
instead of a date, do the inputbox, then convert it into a date. But
I imagine there must be a cleaner way of doing that??
Thanks!!
-Matt
I don't quite understand.
I do not quite understand.
Dim SchedDate As Date
SchedDate = InputBox("Enter a Date")
If I run the above code and put a proper date (ex. 8/17/09), the input
box returns a date value to the SchedDate variable (which is declared
as a date data type). I can run date functions on it, etc. It acts
like a date, yet I never did any conversion from a string. If an
input box always returns a string, how does this work? I am guessing
visual basic converts the returned string into a date automatically?
The input box works fine when a date is entered (no string variable
required). The problem is when the user clicks the cancel button.
It is actually not returning a zero-length string "" in my case. It
is somehow returning 12:00:00 AM. I assume it has something to do
with the variable being assigned as a date data type. I admit the
behavior is kind of strange. When scouring various forums for the
solution, I came across one other person that ran into the same
problem, but unfortunately, it was not answered.
That's what I was trying to avoid...adding another variable to the
mix. I thought maybe there was a cleaner way to do it.
I think I would still rather use the code...
If StrPtr(SchedDate) = 0 Then '--If user clicks cancel
Exit Sub
End If
Since the date that I am gathering is optional, sometimes a user will
just click on OK button without entering anything. That returns a
"". With your code, if they choose not to enter a date, it will
cancel the program. But the StrPtr function allows for the user to
skip over the inputbox if need be.
Perhaps the only workaround is to use your code, adding a string
variable to the mix. I was just wondering if there was something I
was missing...a cleaner way of handling the cancel button when getting
a date from an input box. Any explanations out there would be
appreciated.
Thank you for the code, I may just have to go with that.
I tried using the suggested code, but ran into the followingproblem...
I tried using the suggested code, but ran into the following
problem...
If the user does not enter a valid date...for example: 2/30/09, when I
try to assign SchedDate (date data type) to SchedDateString (string
data type), it gives me a mismatch error again. How can I validate if
a user enters a proper date?
I tried the following:
Do until ValidEntry = true
If IsDate(CDate(SchedDateString)) Then '***I get the type
mismatch error here
ValidEntry = True
SchedDate = SchedDateString
Else
ValidEntry = False
MsgBox "Please make sure to enter a valid date.", vbOKOnly,
"Error"
End If
loop
or even if i just do the following...if it is not a proper date, it
gives mismatch error.
SchedDate = SchedDateString
Once again...any help is greatly appreciated.
Thanks again!!
Hi Pesach,Thanks for your help.
Hi Pesach,
Thanks for your help. I wound up modifying your code a little bit and
it is working great. This is what the final code looks like:
'----------------Get scheduled date via input box-------------------'
Dim SchedDate as Date
Dim SchedDateString, msg, Title as String
ValidEntry = False
Do Until ValidEntry = True
msg = "Enter Scheduled date (for ex: " & Format(Now, "m/d/yy") & ")"
Title = "Scheduled Date"
SchedDateString = (InputBox(msg, Title))
If StrPtr(SchedDateString) = 0 Then '--If user clicks cancel--'
Exit Sub
End If
'--Checks to see if user hit OK button w/out filling in box--'
If SchedDateString = "" Then
msg = "This field cannot be left blank."
Title = "Required Field"
MsgBox msg, vbOKOnly, Title
ElseIf IsDate(SchedDateString) Then
SchedDate = (SchedDateString)
ValidEntry = True
Else
msg = "The date that you entered is not valid. Please try again."
Title = "Invalid Date"
MsgBox msg, vbOKOnly, Title
ValidEntry = False
End If
Loop
'-------------------------------------------------------------------------------'
I added the check: If StrPtr(SchedDateString) = 0 Then exit sub
instead of checking for an empty string.
This link,
http://www.developer.com/net/vb/article.php/1541851,
explains the benefits of using StrPtr.
Unfortunately, if you use: If SchedDateString = "", it does not
discriminate whether the user hit cancel, or if they just hit enter
without typing anything in. In both cases, an empty string is
returned.
Also, by using the IsDate function, I no longer had to trap any
errors, just had to handle if IsDate = false.
'-----------------------------------------------------------------------------'
And for future prosperity, in case someone comes looking for the same
solution down the road...another solution that I found to the problem
without using another string variable:
Dim SchedDate as Date
SchedDate = cDate((InputBox(msg, Title))
This converts it on the fly with no mismatch errors if date entered
correctly. However, you can run into a type mismatch error if the
user does not enter a date (ex. abcd instead of 1/1/09). An even
further downside is that if the user enters a number, such as 12302009
instead of a date, it accepts it and there is no mismatch error. It
converts it to a weird date like 1/1/1890 or 12:00:00 AM. The IsDate
function checks for an ACCURATE date in date format, so the first
solution is really preferable. However one could use your error
trapping code that you wrote to catch the mismatch error and then
follow-up with the IsDate function. But you can only check for an
emtpy string...StrPtr function would not work to see if they hit
cancel since it would throw a mismatch error. You could only check
for an empty string which could be from hitting cancel or by hitting
o.k. with nothing in it.
------------------------------------------------------------------------------
Thanks again for your help. It certainly sent me in the right
direction.
-Matt
Curious...what does GoTo 0 do?
Curious...what does GoTo 0 do?
On Error Goto 0 resets the error handler to its default setting, i.e.
On Error Goto 0 resets the error handler to its default setting, i.e. do
not handle the error.
The original versions of the BASIC language (back in the 1960s IIRC)
required statements to have line numbers. You could only "GOTO" a line
number. If you could set up an error trap, it either had to GOTO a line
number (not a label with n alphanumeric name), or perhaps to GOSUB to a
subroutine that started at a line number. ON ERROR GOTO 0 was a
convention that switched off error handling because you could not have a
line number 0.
e.g. you might have
10 LET X = 0
20 REM set up an error handler
30 ON ERROR GOTO 100
40 REM do something nasty here and execution transfers to line 100
50 REM do not trap errors any more
60 ON ERROR GOTO 0
70 REM do something here
100 REM handle your errors here
I may be getting severl old dialects of BASIC mixed up there!
Peter Jamieson
http://tips.pjmsn.me.uk
mattchorno wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorial...d8-3e2773fa29b5/whats-new-for-developers.aspx