|
|
TextBox Validation - Time Format |
|
Author |
Message |
mmcacorson
|
Posted: Mon Feb 06 15:35:46 CST 2006 |
Top |
Excel Programming >> TextBox Validation - Time Format
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!)
Excel298
|
|
|
|
|
KDales
|
Posted: Mon Feb 06 15:35:46 CST 2006 |
Top |
Excel Programming >> TextBox Validation - Time Format
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
> 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!)
>
|
|
|
|
|
Leith
|
Posted: Mon Feb 06 19:11:52 CST 2006 |
Top |
Excel Programming >> TextBox Validation - Time Format
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?action=getinfo&userid=18465
View this thread: http://www.excelforum.com/showthread.php?threadid=508912
|
|
|
|
|
|
|