Solved: Is this a good way to check if a cell contains a valid time?

[EirikDaude]

Title says it all. Is there some other checks it'd be a good idea to add?
Function wbIsTime(r As Range) As Boolean
If IsNumeric(r.Value) And TypeName(r.Value) = "Double" Then
wbIsTime = True
Else
wbIsTime = False
End If
End Function

[snb]

Why checking afterwards instead of offering valid choices beforehand ?


function time_snb(c00)
time_snb=IsDate(c00)
end function

[EirikDaude]

Thanks for the reply.

I already have data validation on the cells in question, in order to give a warning if someone enters something which is not a time. However, just in case someone does so anyway, I want my code to check the cells' content before trying to do any arithmetic on them :)

As for your code I'm afraid I don't understand it, would you please add an explanation?

[snb]

Do not bother users with messages, offer them valid choices (in a combobox for example).

For many examples (incliding offering valid timevalues) see:
http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[EirikDaude]

I think the list would get a bit longer than most people would prefer - unless there is some combination of the datepicker and a timepicker somewhere? I suppose it wouldn't be too hard to generate a form such as that, but frankly I would prefer not to :P

[snb]

Most input dates lie in a range +- a week from today; most time input between 08:00 and 21:00.

[EirikDaude]

In this case the input time lies between -16 to + 72 hours of the value in the adjacent cell, it should be at least exact to the hour, probably to the nearest half hour.

-edit- I came up with a possible solution which you can have a look at in the attached workbook, but I'm uncertain if this will be more annoying than having the occassional warning from the data validator.

[snb]

Since the input will be done in the worksheet so it seems, you can make a validationlist with valid timevalues. The checking will be done by Excel's builtin facility datavalidation. No need to write elaborate VBA.

[EirikDaude]

Yeah, that is more or less what I've done (I think), but just in case a user ignores the validationlist I wanted the code to check for valid values before performing any operations on it. I added an edit to my previous post, for another possible solution too. Still possible to input invalid data, but you have to really try.

-edit- After some further testing it looks like this actually prevents invalid data from being entered.

[SamT]

Where A1 is the adjacent Cell. You may have to use [R-1C] notation instead of A1 if you're validating more than one Cell :dunno

[snb]

You bet !

[SamT]

Oops! :o:

[RC-1]

[EirikDaude]

Thanks a lot for the help :) I'll try out your solution as well :)