There really isn't a good solution to this. In general, I do all
my controls validation when the user hits the OK/Submit button,
and return focus to offending control if an error is found. The
Exit event is near worthless. Since you don't know the control to
which focus will be transferred, you can "exempt" certain
controls.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Indiana Epilepsy and Child Neurology"
<BrainNoSpamChild@SkylerNoSpam.com> wrote in message
news:ht5ta2ppf3m3vatdcavgm95926643p3k7p@4ax.com...
> 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.