Wondering if there is a simple solution to this one (I really hope so!). I
am using a serious of userforms to collect data and write it into the
spreadsheet, some of the data to be collected is in Time or Date format. I
have found a way to check the users entries as far as dates go, so they are
forced to enter hh/mm/yy, is there something similar does anybody know that I
can use to check the format of the time entries, ie hh:mm. I'm really
struggling with this one!

I know there are calendar and date pickers but with the number of fields
involved I just wanted to keep it simple - famous last words I know!

Thanks in advance to someone much more clever than I!
--
Zani
(if I have posted here, I really am stuck!)

Assuming you want to check the value of a String variable (set by the
inputbox) called UserInput, here is one easy way to check:
If (UserInput like "*#:##") And IsDate(UserInput) Then...

(would be a little harder if you wanted to allow am/pm but for "military
time" the above should work)
--
- K Dales


"Zani" wrote:

Wondering if there is a simple solution to this one (I really hope so!). I
am using a serious of userforms to collect data and write it into the
spreadsheet, some of the data to be collected is in Time or Date format. I
have found a way to check the users entries as far as dates go, so they are
forced to enter hh/mm/yy, is there something similar does anybody know that I
can use to check the format of the time entries, ie hh:mm. I'm really
struggling with this one!

I know there are calendar and date pickers but with the number of fields
involved I just wanted to keep it simple - famous last words I know!

Thanks in advance to someone much more clever than I!
--
Zani
(if I have posted here, I really am stuck!)

Hello Zani,

Since you are using multiple User Forms, the best approach is to use a
macro in a module that can be shared. Place this code in a VBA module
and call it from your code as in the example.

Calling the Macro:
CheckTimeEntry Textbox1

Just replace Textbox1 with the name of the TextBox whose entry you want
to check. If you want to check a TextBox on another User Form, the User
Form doesn't need to be visible, but must be loaded in memory and you
need to use the Form name when calling the macro.

Checking a TextBox on another Form:
CheckTimeEntry UserForm2.TextBox1
_______________________________

Public Sub CheckTimeEntry(ByRef Text_Box As MSForms.TextBox)

Dim MyTime As Date

On Error GoTo InvalidEntry
MyDate = CDate(Text_Box.Text)

Exit Sub

InvalidEntry:
MsgBox "The time value you entered is not valid."
Text_Box.Text = ""

End Sub
_______________________________

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=508912