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.

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"
wrote in message
...
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.

you can "exempt" certain
should be
you can't "exempt" certain


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


"Chip Pearson" wrote in message
...
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"
wrote in message
...
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.