My googling shows this issue has been discussed a couple times but I
haven't found any real solutions.

Sometimes I need the value of a TextBox to be checked when the focus
moves away from the TextBox to another control on the UserForm, except
for certain specific other controls. Examples of those "exceptional"
controls might be a command button which discards the UserForm
contents, or one which sets the TextBox value to a known valid value;
however, the "exceptional" control may not be a CommandButton, so
TakeFocusOnClick=False doesn't solve the problem (and produces unusual
UI behavior which the user may not expect, which I consider
undesirable).

If the TextBox value does get checked and is invalid, I need to return
the focus to the TextBox (possibly, but not necessarily, after
displaying a message or sounding an alarm). If the TextBox value gets
checked and is valid, or if the "target" control is one of the
"exceptional" controls so the value does not get checked, the focus
change to the "target" control should be allowed.

The TextBox Exit and BeforeUpdate events allow the focus change to be
aborted by setting the Cancel parameter to True, but there seems to be
no way to tell what control is being moved to during those events.
ActiveControl still refers to the TextBox, and the tab order does not
help because a mouse click won't necessarily be to the next or
previous control in the tab order.

The Enter event of the target control gave me some hope. With some
work I was able to defer checking the TextBox value until the Enter
event, where I could decide whether to skip validation. The Enter
event doesn't have a Cancel parameter, so if the validation fails I
tried invoking the SetFocus method of the TextBox. Single-stepping
through the code shows it works, except that when the Enter event
subroutine finishes, the focus is set to the new control regardless of
what happens inside the handler!

In short, the problem is that before the Enter event I can't tell
where the focus is going so I can't tell whether to validate or not,
and in the Enter event I can't prevent the focus change if validation
fails. By the time I can do one part of my validation logic, I can't
do another part of it. For my purposes I can't wait until an entire
form gets closed to validate, nor can I prevent invalid entries via
the TextBox Change event.

For a CommandButton I could conceivably do something in the Click or
MouseDown event; coupled with some kind of contortion with the TextBox
KeyDown event I might be able to catch moving off the TextBox via
keyboard (using the tab order to predict the target control) and
mouse, by using two entirely different validation systems. Other
controls don't have a Click event, but usually do have a MouseDown. I
haven't explored this yet, because I consider this kind of code
complication to be a VERY BAD THING(tm), and I'm not even sure it will
work - the Exit event by itself, and the Enter event scheme both
looked like they would work until I actually tried them. I've even
considered some kind of timer-based solution (shudder!). If only
there were an event with a Cancel parameter, which fires after the
ActiveControl has changed! Like, an Enter(ByVal Cancel As
ReturnBoolean) event would do the trick.

Please, somebody tell me there's a reasonable solution I just haven't
come up with yet!
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.