Exit event on field not firing when exiting a frame

[NZ VBA Developer]

G'day!

I have several textboxes in a frame and have written code to validate and
format the value in each textbox on Exit. (An example of the code appears
below.) I'm working with the Exit event because I don't want the textbox to
lose focus if an invalid value is entered. And it works pretty well in most
cases. However...

When I tab out of the last textbox in the frame (or click on another control
that's not in the same frame; e.g. select another tab on the userform), the
code doesn't fire. The value in the textbox doesnt' get validated or
formatted. Apparently VBA doesn't recognise the occurence of an Exit event
for a control _inside_ a frame when focus moves _outside_ the frame.

As promised, an example:

Private Sub txtMargin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'*** Check to see if a value has been entered in the textbox
If txtMargin.Value <> "" Then
'*** Remove the percent sign (if there is one) and check to see that what's
leftover is a number
If IsNumeric(fcnExtractNumber(txtMargin.Value)) Then
'*** If it is, carry on
Cancel = False
txtMargin.Value =
FormatPercent((fcnExtractNumber(txtMargin.Value) / 100), 2)
Else
'*** And if it's not, throw an error and retain focus
MsgBox "The Margin must be numeric.", vbCritical, "Facility
Interest Error"
Cancel = True
End If
End If
End Sub

There's a bit of mucking about to 'extract' just the numeric bit from the
value in the textbox because the FormatPercent function (and FormatCurrency
function, which I use on some other fields) adds non-numeric characters to
the value - and of course these cause the IsNumeric function to fail if the
user goes back into a field that already has a value in it and then exits
again without deleting the percent sign or dollar sign.

Any ideas on how to make this work when 'exiting' the frame? Maybe a
separate 'validate & format' procedure that I can call on the Exit event for
the frame itself?

Cheers!

 

[NZ VBA Developer]

I seem to have worked it out. The solution is indeed to create (Boolean)
functions to validate (and format) the values in the textboxes in the frame
and call these functions to set the Cancel argument on the Exit events both
for the various textboxes and the frame - something like this:

'*** Use the validation function to set the Cancel argument on Exit from the
Margin field ***
Private Sub txtMargin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
If frmBAILMENT.Visible = True Then
Cancel = fcnValidateMargin
End If
End Sub

'*** Do the same ops as the 'old' Exit code below ***
Private Function fcnValidateMargin() As Boolean
fcnValidateMargin = False
If txtMargin.Value <> "" Then
If IsNumeric(fcnExtractNumber(txtMargin.Value)) Then
txtMargin.Value =
FormatPercent((fcnExtractNumber(txtMargin.Value) / 100), 2)
fcnValidateMargin = False
Else
MsgBox "The Margin must be numeric.", vbCritical, "Facility
Interest Error"
fcnValidateMargin = True
End If
End If
End Function

'*** Use the same validation functions for the fields to set the Cancel
argument on Exit from the frame ***
Private Sub grpBailmentFacInterestDetails_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Cancel = False
If frmBAILMENT.Visible = True Then
If fcnValidateBaseRate Or fcnValidateMargin Then
Cancel = True
Else
Cancel = False
End If
End If
End Sub

A couple of notes on the above:
- The code _doesn't_ fire when selecting another tab on the userform - even
when a control on the tab obviously has focus. Apparently focus is
tab-specific? Or maybe it has something to do with an absence of Enter and
Exit events for a Page of a MultiPage control? Can't see a way around this...
- I check the Visible property of the userform to prevent the code from
firing when the userform is closed and popping the error message if an
invalid value is left in one of the textboxes. This in spite of the fact that
simply selecting another tab doesn't trigger the Exit event for a textbox (as
described above).

Just posting this in case somebody else is having a similar problem.

Cheers!