Elseif statement not continuing (time format textbox) 3

[davedave24]

This is strange. I have a textbox that formats to hh:mm. When the user clicks the button, it determines if the time is in the correct format:

- if it's hh:mm, it checks if the hours are > 23, and mins > 59
- if it's h:mm, it checks if the mins are > 59
- if it's >5 numbers (i.e the user has entered 123:45)

-formatting code is below

The problem is that the simple validation If-ElseIf statement won't go past the Time textbox. It validates all of the above rules, but then won't check if textPostCode is empty (and anything else after this).

'this is the Submit/Ok button
If textTime.Text = "" Then
        MsgBox "Please enter a delivery Time", vbExclamation + vbOKOnly, "Oops!"
        textTime.SetFocus
        
        
    ElseIf Len(textTime) = 5 Then       'hh:mm
        If Mid(textTime, 1, 2) > 23 Then
            MsgBox "Please enter a proper time" & vbCrLf & vbCrLf & "Format: hh:mm", vbExclamation + vbOKOnly, "Not like that!"
            textTime.SetFocus
        ElseIf Mid(textTime, 4, 2) > 59 Then
            MsgBox "Please enter a proper time" & vbCrLf & vbCrLf & "Format: hh:mm", vbExclamation + vbOKOnly, "Not like that!"
            textTime.SetFocus
        End If
    ElseIf Len(textTime) = 4 Then       'h:mm
        If Mid(textTime, 3, 2) > 59 Then
            MsgBox "Please enter a proper time" & vbCrLf & vbCrLf & "Format: hh:mm", vbExclamation + vbOKOnly, "Not like that!"
            textTime.SetFocus
        End If
    ElseIf Len(textTime) > 5 Then       'too many numbers
        MsgBox "Please enter a proper time" & vbCrLf & vbCrLf & "Format: hh:mm", vbExclamation + vbOKOnly, "Not like that!"
        textTime.SetFocus

    
    
    
    ElseIf textPostCode.Text = "" Then
        MsgBox "Please enter a Post Code", vbExclamation + vbOKOnly, "Oops!"
        textPostCode.SetFocus

End If

Extra: this just formats the time textbox to hh:mm, or to ##:## if the user doesn't enter a colon

Private Sub textTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    textTime.BackColor = &HFFFFFF
    If InStr(textTime.Text, ":") Then
        textTime.Text = Format(textTime.Text, "hh:mm")
    Else
        textTime.Text = Format(textTime.Text, "##:##")
    End If
End Sub

This just stops the user entering anything other than numbers and colon

Private Sub textTime_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case Asc("0") To Asc("9")
        
        Case Asc(":")
     
        Case Asc("-")
            If InStr(1, Me.textTime.Text, "-") > 0 Or Me.textTime.SelStart > 0 Then
                KeyAscii = 0
            End If
        Case Asc(".")
            If InStr(1, Me.textTime.Text, ".") > 0 Or Me.textTime.SelStart > 0 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
End Sub

 

[PHV]

Replace this:
ElseIf textPostCode.Text = "" Then
with this:
End If
If textPostCode.Text = "" Then


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

ho 'bout something like this...

Private Sub CommandButton1_Click()
    Dim a, i As Integer
    
    a = Split(TextBox1.Text, ":")
    
    Select Case UBound(a)
        Case 0
            MsgBox "No time"
        Case 1
            For i = 0 To 1
                Select Case i
                    Case 0
                        Select Case a(i)
                            Case Is < "0"
                                MsgBox "neg hrs"
                            Case Is < "24"
                            
                            Case Else
                                MsgBox "hrs >= 24"
                        End Select
                    Case 1
                        Select Case a(i)
                            Case Is < "0"
                                MsgBox "neg mins"
                            Case Is < "60"
                            
                            Case Else
                                MsgBox "min >= 60"
                        End Select
                End Select
            Next
        Case Else
            MsgBox "only enter hrs : mins"
    End Select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davedave24]

I've tried that PHV - it tests the Time, then the Postcode all in one. For example, if the time is bad/blank, it displays 2 Messageboxes - 'please enter the time', 'please enter the post code'.

Is there a way to stop it continuing if it trips up on the Time?

 

[davedave24]

Skip - this works except if the user enters an hour less than 10... if they enter 8:45, the "hrs > 24" error trips.

 

[PHV]

if the time is bad/blank, it displays 2 Messageboxes
Simply add Exit Sub after each textTime.SetFocus

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

Thanks PHV, that works nicely, and I just learnt a new command!

 

[SkipVought]

replace the TWO Select case statements

Select Case Format(a(i), "00")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

or this...

Private Sub CommandButton1_Click()
    Dim a, i As Integer
    
    a = Split(TextBox1.Text, ":")
    
    Select Case UBound(a)
        Case 0
            MsgBox "No time"
        Case 1
            For i = 0 To 1
                Select Case i
                    Case 0
                        Select Case Val(a(i))
                            Case Is < 0
                                MsgBox "neg hrs"
                            Case Is < 24
                            
                            Case Else
                                MsgBox "hrs >= 24"
                        End Select
                    Case 1
                        Select Case Val(a(i))
                            Case Is < 0
                                MsgBox "neg mins"
                            Case Is < 60
                            
                            Case Else
                                MsgBox "min >= 60"
                        End Select
                End Select
            Next
        Case Else
            MsgBox "only enter hrs : mins"
    End Select
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

You could also let VBA do the parsing dirty-work for you:

Private Sub CommandButton1_Click()
    Dim dt As Date
    
    On Error Resume Next
    dt = TimeFromString(TextBox1.Text)
    If (Err.Number > 0) Then MsgBox Err.Description

End Sub

Public Function TimeFromString(s As String) As Date
    
    On Error Resume Next

    If (Not s Like "*:*") Then Err.Raise 65001, "", "Error - Missing time entry"

    TimeFromString = CDate(s)
    If (Err.Number > 0) Then Err.Raise Err.Number, Err.Source, "Error - Invalid time entry", Err.HelpFile, Err.HelpContext
    If (TimeFromString >= CDate(1)) Then Err.Raise 65001, "", "Error - Time contains date information."
    If (Not Format(TimeFromString, "hh:mm:ss") Like "##:##:00") Then Err.Raise 65002, "", "Error - Time contains seconds."

End Function

 

[davedave24]

Thanks everyone, Skip, Dave, GBH.

Dave I'm not advanced enough to understand your code yet but thanks

 

[davedave24]

Also - Skip's second code works perfectly