Validate text box entry on user form

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

I have text box on a user form, the user is to enter time taken to do a
certain task in it.

I want the time to be entered in time format - h:mm

The number of hours can be 0 to 100

In the OK click code I want to check if the entry is correct, that is, 3rd
from the right is a':' and the last two digits are less than 60

Examples:
9:45 OK
9:75 Not OK

The entry is then put into a cell within the file.

Thanks in advance.

Gareth
 
Gareth said:
I have text box on a user form, the user is to enter time taken to do a
certain task in it.

I want the time to be entered in time format - h:mm

The number of hours can be 0 to 100

In the OK click code I want to check if the entry is correct, that is, 3rd
from the right is a':' and the last two digits are less than 60

Examples:
9:45 OK
9:75 Not OK

The entry is then put into a cell within the file.
Click to expand...
Private Sub CommandButton1_Click()
Dim ok As Boolean, arr As Variant
ok = False
If TextBox1 Like "*#:##" Then
arr = Split(TextBox1, ":")
If IsNumeric(arr(0)) Then
If arr(0) <= 100 And arr(1) < 60 Then
ok = True
End If
End If
End If
If ok Then ActiveSheet.Cells(1, 1).Formula = TextBox1
End Sub
 
Gareth,

Private Sub TextBox1_AfterUpdate()
'Is time formatted correctly?
If Mid(TextBox1.Text, Len(TextBox1.Text) - 2, 1) = ":" And _
Right(TextBox1.Text, 2) < 60 Then
Sheets("sheet1").Range("Z99") = TextBox1.Text
Else
'Not formatted correctly, so tell user.
MsgBox "Time MUST contain a colon ':' and be in Hours and Minutes",
"Error"
End If
End Sub

Private Sub CommandButton1_Click()
'Check that time has been saved.
'if it hasn't, then user has ignored message box!
'don't exit form until time entered correctly
If Sheets("sheet1").Range("Z99") = TextBox1.Text Then Unload UserForm1

End Sub
 
Back
Top