Dates in Userforms (MS Word 2000 on a PC)

[Beth Brooks]

Hi!

Let me preface this by saying that while I've used Word daily for 15 years,
I have used VBA for only two days and haven't programmed anything since
Freshman level Fortran in (cough) 1989. Assuming I know NOTHING about
programming would be quite fair. I saw a post that discussed date validation
in userforms but didn't understand the answer. So I'm going to rephrase the
question and hope for the best.

I have created a userform with fields that prompt the user for a title,
document number, and version date at file creation. I'll use bookmarks to
insert the data the user provides in various places through the document. I
am working on creating a userform that will prompt the user to either enter a
new version date or assert that no changes will be made to the file; this
userform will apply each time the file is opened. I would like to validate
that the date entered in either userform is in the correct format (dd MMM
yyyy) and I would like to be sure that the date entered in the second
userform (at file open) overrides the date entered in the first userform (at
file creation).

I am constrained to using Microsoft Word 2000 on a PC.

Thanks in advance for your assistance!

 

[Jay Freedman]

On Fri, 20 Jan 2006 18:16:03 -0800, "Beth Brooks" <Beth

Hi!

Let me preface this by saying that while I've used Word daily for 15 years,
I have used VBA for only two days and haven't programmed anything since
Freshman level Fortran in (cough) 1989. Assuming I know NOTHING about
programming would be quite fair. I saw a post that discussed date validation
in userforms but didn't understand the answer. So I'm going to rephrase the
question and hope for the best.

I have created a userform with fields that prompt the user for a title,
document number, and version date at file creation. I'll use bookmarks to
insert the data the user provides in various places through the document. I
am working on creating a userform that will prompt the user to either enter a
new version date or assert that no changes will be made to the file; this
userform will apply each time the file is opened. I would like to validate
that the date entered in either userform is in the correct format (dd MMM
yyyy) and I would like to be sure that the date entered in the second
userform (at file open) overrides the date entered in the first userform (at
file creation).

I am constrained to using Microsoft Word 2000 on a PC.

Thanks in advance for your assistance!

Hi Beth,

First, don't worry about being constrained to Word 2000. The parts of
VBA used in date validation haven't changed since then.

VBA has a built-in function named IsDate. You pass in an expression
(usually a string variable) as the parameter, and it returns True if
the expression is recognized as a date or False otherwise. For example

IsDate("1/20/2006")
IsDate("20/1/2006")
IsDate("20 Jan 2006")
IsDate("Jan 20, 2006")

all return True, while

IsDate("1/32/2006")
IsDate("32/1/2006")
IsDate("Jan 32, 2006")
IsDate("20 Fred 2006")
IsDate("XYZ")

all return False.

The difficulty here is that IsDate is willing to accept both
MM/dd/yyyy and dd/MM/yyyy formats, so you're never quite sure the
month and day are in the right order. A date in either of these
formats is rejected only if it's invalid in both formats (as shown by
the first two examples in each group above). If that's a problem for
your users, the only good solution is to use a separate text input box
in the userform for each part of the date. I'll assume that isn't
necessary.

In your userform's code window, select the name of the date text box
in the left-hand dropdown just below the toolbar. Then select the Exit
event from the right-hand dropdown. This creates the first and last
lines of the "Exit event handler" for the text box; for example, with
the default name TextBox1, you'd get

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

This subroutine is automatically called when the user does something
(tab or click) to leave the text box. You put the validation code, and
optionally some reformatting code, between those two lines. The other
thing to notice is that the routine's declaration includes a parameter
named Cancel. If your code sets the value of that parameter to True,
the cursor won't leave the text box, and the user has to try again. So
the validation code might look like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(TextBox1.Text) Then
MsgBox "Please enter a valid date."
Cancel = True
Else
TextBox1.Text = Format(CDate(TextBox1.Text), "dd MMM yyyy")
End If
End Sub

If the text in the box doesn't pass the IsDate test, they'll be shown
the message box and, when they click OK there, the cursor will return
to the text box instead of moving on.

The line after the Else runs only when IsDate returns True. It takes
the text string that's currently in the text box (TextBox1.Text) and
passes it into the CDate function. This function creates a Date
expression -- an internal representation of a date as a special data
type. That Date expression is used in turn by the Format function to
make a new string in the format you asked for, regardless of what
format the original input had.

I don't think you need to worry about whether the second userform's
result overrides that of the first userform. If you set up your
template so the first userform is called from the AutoNew or
Document_New macro, while the second userform is called from the
AutoOpen or Document_Open macro, that detail is automatically taken
care of. See
http://www.word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm for an
explanation of these macros.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Beth Brooks]

Jay,

Thank you! Your explanation was easy to follow and helped a great deal. The
properly formatted date shows up in all my new files. I'm still working on
the autoOpen form and macro (the requirements are a bit more complicated now)
but I've got a great start, thanks to you.

Beth

 

[DanielWalters6]

Thank you - I was looking for something to validate in vba a date is real.
ISDATE is perfect!