I have a macro which calculates anniversary and other follow up dates from a
given date in a spreadsheet. There is a prompt in the source cell, but users
repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I
would use a formula like T(A1)=””, TRUE,FALSE to check whether the date is in
string or date format but how do I achieve this programmatically before
sending a message box to the user to change the format?

Thanks a lot

Set up a validation check:
From the Data toolbar menu select:

Data -> Validation; allow Date from the dropdown
And select greater than.
You can also add a custom message to specify the type of data required, plus
format the cells in a specific manner.

Regards,

Michael Arch.


"Jimbob" wrote:

> I have a macro which calculates anniversary and other follow up dates from a
> given date in a spreadsheet. There is a prompt in the source cell, but users
> repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I
> would use a formula like T(A1)=””, TRUE,FALSE to check whether the date is in
> string or date format but how do I achieve this programmatically before
> sending a message box to the user to change the format?
>
> Thanks a lot

Thanks for responding Michael. I understand data validation but the
spreadsheet my macro works on isn't of my design and can't be changed too
redaily as you suggest. My macro works on the sheet once it comes to me.
What I need to do is verify the date is in the correct format or send a
prompt to the user to change it. I imagined using "If . . Then" but can't
get the code for the formula I'd use in Excel before generating the message
box.

"Michael" wrote:

> Set up a validation check:
> From the Data toolbar menu select:
>
> Data -> Validation; allow Date from the dropdown
> And select greater than.
> You can also add a custom message to specify the type of data required, plus
> format the cells in a specific manner.
>
> Regards,
>
> Michael Arch.
>
>
> "Jimbob" wrote:
>
> > I have a macro which calculates anniversary and other follow up dates from a
> > given date in a spreadsheet. There is a prompt in the source cell, but users
> > repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I
> > would use a formula like T(A1)=””, TRUE,FALSE to check whether the date is in
> > string or date format but how do I achieve this programmatically before
> > sending a message box to the user to change the format?
> >
> > Thanks a lot

I've now worked out some code which seems to work:
Dim ADate
Worksheets("Summary").Range("E4").Activate
ADate = ActiveCell
If IsDate(ADate) Then
Else
Response = MsgBox("blah, blah, blah", vbCritical, "WARNING")
End If
End Sub

Is this foolproof or should it be cleverer?

Cheers

"Jimbob" wrote:

> I have a macro which calculates anniversary and other follow up dates from a
> given date in a spreadsheet. There is a prompt in the source cell, but users
> repeatedly enter the date as a string (3rd Aug 07 instead of 3/8/07). I
> would use a formula like T(A1)=””, TRUE,FALSE to check whether the date is in
> string or date format but how do I achieve this programmatically before
> sending a message box to the user to change the format?
>
> Thanks a lot