Assigning the Cancel argument on an Exit event for a textbox

[NZ VBA Developer]

I have a textbox on a userform that needs to be validated to ensure that the
value entered is numeric and that exactly 8 numbers have been entered, and
then format the value appropriately (NZ IRD Number: nn-nnn-nnn). I've written
a function to do the validation and formatting that is called on the Exit
event for the textbox, and it works fine except for one small problem: I
can't get focus to stay in the textbox if the validation fails.

I see in the VBA help that the syntax for Exit events is:
"Private Sub object_Exit( ByVal Cancel As MSForms.ReturnBoolean)"
and there is a note that says:
"To prevent the control from losing focus, assign True to the Cancel
argument of the Exit event."
However, I can't work out the syntax for actually assigning True to the
Cancel argument. The VBA compiler throws an error no matter what I try. Any
suggestions?

 

[Doug Robbins - Word MVP]

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim one As Long, two As Long
one = 1
two = 1
If one = two Then
Cancel = False
Else
Cancel = True
End If
End Sub

With the above, the textbox loses focus; with the following, it retains
focus

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim one As Long, two As Long
one = 1
two = 2
If one = two Then
Cancel = False
Else
Cancel = True
End If
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[NZ VBA Developer]

Brilliant! Not exactly a perfect fit for my purposes, but it certainly puts
me on the right track. Just a matter of working out how to get the validation
process to set the Cancel argument. I think the function needs a bit of
revision as it's currently trying to both validate _and_ format the value in
the textbox. The validation should probably be in the Exit event code and
formatting separated out (which is what I would have done in the first place
if I had written the original code - the dangers of working with inherited
code written by a novice).

Thanks Doug! I'll let you know what the final product looks like.

 

[NZ VBA Developer]

This is what I came up with in the end:

Private Sub txtGSTNum_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False

If Not bExit Then
If txtGSTNum.Value <> "" Then
Dim Counter As Integer, intGSTLength As Integer, strCurrentValue As String
intGSTLength = Len(txtGSTNum.Value)

If intGSTLength = 8 Or intGSTLength = 10 Then
Dim myGST As String
myGST = txtGSTNum.Value
Else
fcnGSTError
Cancel = True
Exit Sub
End If

If intGSTLength = 8 Then
For Counter = 1 To intGSTLength
strCurrentValue = Mid(myGST, Counter, 1)
If Not IsNumeric(strCurrentValue) Then
fcnGSTError
Cancel = True
Exit Sub
End If
Next
txtGSTNum.Value = Left(myGST, 2) & "-" & Mid(myGST, 3, 3) & "-"
& Right(myGST, 3)
ElseIf intGSTLength = 10 Then
For Counter = 1 To intGSTLength
strCurrentValue = Mid(myGST, Counter, 1)
If Counter = 3 Or Counter = 7 Then
If strCurrentValue <> "-" Then
fcnGSTError
Cancel = True
Exit Sub
End If
Else
If Not IsNumeric(strCurrentValue) Then
fcnGSTError
Cancel = True
Exit Sub
End If
End If
Next
End If
End If
End If
End Sub

Two conditions need to be accommodated: a single, 8-digit number or
"nn-nnn-nnn" (as well as no value). Thus the reason for checking the length
of the value and different evaluation processes for each length.

fcnGSTError is just a 1-line function that pops a message box. I probably
didn't need it, but I may want to do more than just generate a warning in the
future.

bExit is a global Boolean variable (flag) that's set when the Exit button on
the form is clicked so the validation doesn't run if the textbox has focus at
the time.

The clunky legacy validate-n-format function is history, and no need for a
separate format function as it's just 1 line of code anyway.

Onya, mate, for pointing me in the right direction!