Hi,

Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......

12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad

the txtbox for this on the userform are txttime and txtATA

if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.

hope you can help
thanks

I don't think so.

You could validate the input after their done, though.

Or maybe you could use two controls (one for the hours and one for the minutes).

Or even a time picker.
http://www.dailydoseofexcel.com/arch...3/time-picker/

Anthony wrote:

Hi,

Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......

12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad

the txtbox for this on the userform are txttime and txtATA

if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.

hope you can help
thanks


--

Dave Peterson

Hi,

Try this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Format(Me.TextBox1.Value, "hh:mm")
End Sub

Mike

"Anthony" wrote:

Hi,

Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......

12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad

the txtbox for this on the userform are txttime and txtATA

if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.

hope you can help
thanks

Why not just put the colon in for them? If your TextBox is named TextBox1,
then this will take the assign the time value either way it is entered...

TheTimeValue = Format(Replace(TextBox1.Value, ":", ""), "00:00")

More important, I would think, is to make sure there are only numbers and
one colon maximum inputted. Something like this maybe...

If Len(TBvalue) - Len(Replace(TBvalue, ":", "")) 1 Then
MsgBox "You have too many colons!"
ElseIf Not TBvalue Like "*:##" Then
MsgBox "You do not enough digits after the colon!"
Else
TBvalue = Replace(TBvalue, ":", "")
If TBvalue Like "*[!0-9]*" Then
MsgBox "Enter digits and one colon only!"
ElseIf Len(TBvalue) 4 Then
MsgBox "You have too many digits!"
Else
TheTimeValue = Format(Replace(TBvalue, ":", ""), "00:00")
End If
End If



Rick


"Anthony" wrote in message
...
Hi,

Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......

12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad

the txtbox for this on the userform are txttime and txtATA

if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.

hope you can help
thanks

You could validate the input after they're done, though.
(grammar police)

Dave Peterson wrote:

I don't think so.

You could validate the input after their done, though.

Or maybe you could use two controls (one for the hours and one for the minutes).

Or even a time picker.
http://www.dailydoseofexcel.com/arch...3/time-picker/

Anthony wrote:

Hi,

Is there a way to 'force' a user to input the time on my userform in the
format xx:xx, ie 24hrs with ':' between the hours and minutes
for example......

12:23 is good
09:12 is good
8:15 is good
1223 is bad
0912 is bad
815 is bad

the txtbox for this on the userform are txttime and txtATA

if the wrong format is used I would ideally like a msgbox to appear to
advise wrong format used.

hope you can help
thanks


--

Dave Peterson

--

Dave Peterson