DeanL said:
Hi guys,
I have a userform that has several textboxes on and need some of them
to force the user to enter data in a specified format (i.e. m/d/
yyyy). I can format the data once it's been captured but I'm not sure
how to force the format during input.
Many thanks for any help.
The easiest and most foolproof method is to use a date picker control. In
the VBA editor, right-click a blank area in the Toolbox and click Additional
Controls. In the dialog, check the box for "Microsoft Date and Time Picker
Control" to add its icon to the toolbox. If that item isn't in the list,
visit
http://support.microsoft.com/kb/297381 for instructions.
The date picker looks like a text box with a dropdown arrow; when you click
the arrow, a calendar page appears. Clicking a date on the calendar places
the corresponding date in the text box. In the Properties pane of the date
picker, you can choose a value for the Format property to get a long date or
short date (as defined on the computer where the control is being used,
which may be different from the one on your PC), a time, or a custom format.
If you choose the custom format, you also have to supply the format string
in the CustomFormat property.
The only drawback is that the support file for the date picker
(mscomct2.ocx) may not be on every user's computer, so you would need to
include the link to the KB article I cited.
The next best approach is to put three separate text boxes on the userform,
one for day, one for month, and one for year. You can include a spinbutton
control next to each box to enable up/down clicking, but that requires code
in the userform to connect the spinbutton to its text box. You also need
validation code in each text box's Change procedure to ensure that the value
is a number and is within the proper bounds for its box.
If you allow free-form text entry into a single text box, there is no
practical way to enforce formatting. For example, if both the first and
second numbers in the entered string are less than 13, how do you know
whether the user intended the first one to be the month or the day?
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
