Checking format on input

[Guest]

Hi I am writing a macro in VisualBasic 6.3. In an Excel-spreadsheet the user
is supposed to write two dates in two different cells. When pressing a button
defined in my macro the dates submitted by the user shall be stored as a
variable and checked. The checking is to see that the date is written
properly e.g. YYYY-MM-DD. How do I preform this check? Is it possible to see
if the dates gives by the user are non-existing? Any help is appreciated!

 

[Bob Phillips]

How about using Data Validation, which has a date type, and you can specify
upper and lower limits on the date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Hi Jenni,

You could use Excel's data validation on the date input cells to catch any
errors right at the input stage. The only caveat is that if a user copies
and pastes in a date from another cell location which doesn't have
validation, this will wipe out the existing validation test (a bug in my
opinion).

If you're reasonably certain your users won't be pasting input values into
the validated date cells then data validation is the way to go.

Otherwise you can do a high-level check in VBA to ensure that the entered
values are indeed dates by using the IsDate function:

If Not (IsDate(Input Range)) Then MsgBox "You must enter a date in the date
field"

You can of course also build your own data validation by checking the
VBA.Year, VBA.Month, and VBA.Day values against whatever criteria you'd like.