Focus

[Harlan Grove]

Dave Unger wrote...

The reason I was asking - the Format line seems to work whether CLng is
present or not. As I'm still on the steep part of the learning curve,
I probably wouldn't have realized the need for it.

Format appears to be smart enough to convert numeric text (i.e., stuff
for which IsNumeric returns TRUE and VarType returns vbString).
Therefore, no type conversions appears to be needed when calling
Format.

That said, Format(x, "00:00") seems to treat the second argument the
same as "00\:00", so just inserts a colon when it can convert x to a
number. Then the worksheet function TIMEVALUE applies Excel's date/time
semantics in which 9:99 would be converted to 9 hours 99 minutes = 10
hours 39 minutes, so automatically converted to 10:39.

If you're *ALWAYS* entering times without colons and without AM/PM, so
always in [h]hmm format where hours could range from 0 or 00 to 24,
then you'd be *MUCH* better off using simple *TEXT* *PATTERN* tests for
valid time entries.


Time.Value = Trim(Time.Value)
If Not(Time.Value Like "2[0-4][0-5][0-9]" _
Or Time.Value Like "[01][0-9][0-5][0-9]" _
Or Time.Value Like "[0-9][0-5][0-9]") Then
MsgBox "Invalid time entry: " & Time.Value
Time.Value = ""
Cancel = True
End If