To fill out a form i'm asking the user to input time in 24 hour notation
in an inputbox. For example 13:30 or 21:45 or 08:30. Most users do that
correctly but some are messing things up by inputting all kinds of
invalid formats, something like:

08;30 (semicolon)
08.30 (dot)
08,30 (comma)
8 hour and 30 minutes (text)
"My start time is 8 o'clock and thirty minutes" (text, complete sentence
which is BS!)

So i'm looking for a routine thats checks the input and format that
input as hh:mm. When the users input is invalid - send him back to the
inputbox for a new attempt. Keep sending him back till the input is
valid (hh:mm).

--
With Kind Regards,
Bret Bernever

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"

This is an outline of the VBA solution,
1. do a "split" with delimiter ':' on the input and use "ubound" to check
that there are two fields.
2. check each field f with the operator f like "##" to verify it is a two
digit number.
3. check that the first field numeric value is 0-23 and second is 0-59.

HM

"Bret Bernever" wrote in message
...
To fill out a form i'm asking the user to input time in 24 hour notation
in an inputbox. For example 13:30 or 21:45 or 08:30. Most users do that
correctly but some are messing things up by inputting all kinds of
invalid formats, something like:

08;30 (semicolon)
08.30 (dot)
08,30 (comma)
8 hour and 30 minutes (text)
"My start time is 8 o'clock and thirty minutes" (text, complete sentence
which is BS!)

So i'm looking for a routine thats checks the input and format that
input as hh:mm. When the users input is invalid - send him back to the
inputbox for a new attempt. Keep sending him back till the input is
valid (hh:mm).

--
With Kind Regards,
Bret Bernever

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"

Have you thought about using a different technique to get the time--maybe a
couple of spinners or scrollbars?

Bret Bernever wrote:

To fill out a form i'm asking the user to input time in 24 hour notation
in an inputbox. For example 13:30 or 21:45 or 08:30. Most users do that
correctly but some are messing things up by inputting all kinds of
invalid formats, something like:

08;30 (semicolon)
08.30 (dot)
08,30 (comma)
8 hour and 30 minutes (text)
"My start time is 8 o'clock and thirty minutes" (text, complete sentence
which is BS!)

So i'm looking for a routine thats checks the input and format that
input as hh:mm. When the users input is invalid - send him back to the
inputbox for a new attempt. Keep sending him back till the input is
valid (hh:mm).

--
With Kind Regards,
Bret Bernever

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"

--

Dave Peterson

In article ,
says...

Have you thought about using a different technique to get the time--maybe a
couple of spinners or scrollbars?

Dave: Thanks for thinking with me. Good tip, didn't even crossed my
mind. Comes in very handy.

Luke: Nice approach. I'll try that out.

Thank you both for cooperating on this one.


--
With Kind Regards,
Bret Bernever

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"