I have a userform that has a text label that reports the calendar date/
time in the format of the activecell. All works fine when the cell has
been formated to a date format but it gets reported back in various
odd ball forms when in General format, currency, %, fraction etc.

This occurs when the cell has not yet been formated to a date
It there a way I can used to to determine if the cell is in a date
format so I can change the label in these situations

The isdate function will tell if the cell is a date but I need to know
the cell format before data is entered.

TIA

Therre is no reason you can't format the cell before data is entered.

"(E-Mail Removed)" wrote:

> I have a userform that has a text label that reports the calendar date/
> time in the format of the activecell. All works fine when the cell has
> been formated to a date format but it gets reported back in various
> odd ball forms when in General format, currency, %, fraction etc.
>
> This occurs when the cell has not yet been formated to a date
> It there a way I can used to to determine if the cell is in a date
> format so I can change the label in these situations
>
> The isdate function will tell if the cell is a date but I need to know
> the cell format before data is entered.
>
> TIA
>
>

Yes Joel I could, but my form allows the user to enter the date using
one of 4 pre set formats ( dates, time or date & time) or in the the
format of the existing cell (which presumes it is a date format
already). The label shows them what it would look like, so I need to
know if it is a date format for the label. They can change after if
they want

Can you not ignore format if you create the label using

Format(ActiveCell.Value, "dd mmm yyyy hh:mm:ss")

(or whatever format string you want)...


You could check the NumberFormat property for a particular format, e.g.:

If ActiveCell.NumberFormat Like "*yy*" Then
'likely a date - or use *m/d*, *d/m*, etc
Else
'Do something else
End If

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> I have a userform that has a text label that reports the calendar date/
> time in the format of the activecell. All works fine when the cell has
> been formated to a date format but it gets reported back in various
> odd ball forms when in General format, currency, %, fraction etc.
>
> This occurs when the cell has not yet been formated to a date
> It there a way I can used to to determine if the cell is in a date
> format so I can change the label in these situations
>
> The isdate function will tell if the cell is a date but I need to know
> the cell format before data is entered.
>
> TIA

the important requirment is that it is in any date time format and not other
formats. The date format is just a number with zero equalling Jan 1, 1900
and the whole number 1 represents one day. The different time date formats
just displays this number differently.



"(E-Mail Removed)" wrote:

>
> Yes Joel I could, but my form allows the user to enter the date using
> one of 4 pre set formats ( dates, time or date & time) or in the the
> format of the existing cell (which presumes it is a date format
> already). The label shows them what it would look like, so I need to
> know if it is a date format for the label. They can change after if
> they want
>
>

If the user is making the input through a control on the UserForm, then you
should be able to use the value of the user input/selection to format as soon
as it has been initiated. Format(ControlValue, "d/m/yy") with ControlValue
being substituted with the appropriate code structure and the date format
being changed to what you require.

"(E-Mail Removed)" wrote:

> I have a userform that has a text label that reports the calendar date/
> time in the format of the activecell. All works fine when the cell has
> been formated to a date format but it gets reported back in various
> odd ball forms when in General format, currency, %, fraction etc.
>
> This occurs when the cell has not yet been formated to a date
> It there a way I can used to to determine if the cell is in a date
> format so I can change the label in these situations
>
> The isdate function will tell if the cell is a date but I need to know
> the cell format before data is entered.
>
> TIA
>
>

If you don't want to parse the numberformat property, you could put a number
in it and check the vartype:

Activecell.Value = 1
? vartype(activecell.Value) = vbDate
True

then clearcontents the cell.

--
Regards,
Tom Ogilvy


"(E-Mail Removed)" wrote:

> I have a userform that has a text label that reports the calendar date/
> time in the format of the activecell. All works fine when the cell has
> been formated to a date format but it gets reported back in various
> odd ball forms when in General format, currency, %, fraction etc.
>
> This occurs when the cell has not yet been formated to a date
> It there a way I can used to to determine if the cell is in a date
> format so I can change the label in these situations
>
> The isdate function will tell if the cell is a date but I need to know
> the cell format before data is entered.
>
> TIA
>
>

You can run the text through the datevalue function to determine if it is a
valid date

On Error Resume Next
xyz = DateValue("89:98")
If Err.Number <> 0 Then

MsgBox ("Date entered is not a valid date")
End If

On Error GoTo 0


"Tom Ogilvy" wrote:

> If you don't want to parse the numberformat property, you could put a number
> in it and check the vartype:
>
> Activecell.Value = 1
> ? vartype(activecell.Value) = vbDate
> True
>
> then clearcontents the cell.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "(E-Mail Removed)" wrote:
>
> > I have a userform that has a text label that reports the calendar date/
> > time in the format of the activecell. All works fine when the cell has
> > been formated to a date format but it gets reported back in various
> > odd ball forms when in General format, currency, %, fraction etc.
> >
> > This occurs when the cell has not yet been formated to a date
> > It there a way I can used to to determine if the cell is in a date
> > format so I can change the label in these situations
> >
> > The isdate function will tell if the cell is a date but I need to know
> > the cell format before data is entered.
> >
> > TIA
> >
> >