I'm looking for a way to take actions the groups of events related to
a user action. For instance, if a TextBox is active and the user
clicks on a CommandButton, then on a ListBox, I want to take action
_after_ (not during) the last event related to the CommandButton
click, but _before_ the first event related to the ListBox click.
Using the handler for the last event from the first click or the first
event from the second click isn't what I'm looking for.

Using OnTime with Now and no offset looked promising until I realized
that if I do the two clicks quickly the subroutine set in the OnTime
doesn't get called until after the series of events related to the
second click. DoEvents doesn't seem to help with this situation at
all.

I'm considering looking into using an API timer, but I'm concerned it
may have the exact opposite problem, and run the subroutine before the
events resulting from the first click have all completed.

Any suggestions for what general direction to go are appreciated.
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.

if you can't code your conditions within the existing events, then I doubt
you can do it. I don't see any role for an API timer but then I don't know
what you actually want to accomplish.

why can't you call your routine from the appropriate event.

--
Regards,
Tom Ogilvy


"Indiana Epilepsy and Child Neurology" wrote:

I'm looking for a way to take actions the groups of events related to
a user action. For instance, if a TextBox is active and the user
clicks on a CommandButton, then on a ListBox, I want to take action
_after_ (not during) the last event related to the CommandButton
click, but _before_ the first event related to the ListBox click.
Using the handler for the last event from the first click or the first
event from the second click isn't what I'm looking for.

Using OnTime with Now and no offset looked promising until I realized
that if I do the two clicks quickly the subroutine set in the OnTime
doesn't get called until after the series of events related to the
second click. DoEvents doesn't seem to help with this situation at
all.

I'm considering looking into using an API timer, but I'm concerned it
may have the exact opposite problem, and run the subroutine before the
events resulting from the first click have all completed.

Any suggestions for what general direction to go are appreciated.
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.

While I was dwelling on the fundamental interconnectedness of all
things on Tue, 15 Aug 2006 09:53:01 -0700, Tom Ogilvy
wrote:

if you can't code your conditions within the existing events, then I doubt
you can do it. I don't see any role for an API timer but then I don't know
what you actually want to accomplish.

why can't you call your routine from the appropriate event.

OK, I admit it; I still seek the holy grail of "on the fly validation"
- checking the data as the focus leaves the control, and (sometimes)
forcing the user to go back ahd "do it right this time". I actually
already have a good framework of classes to support this, and it
really works, except for one remaining problem.

Using the existing events causes a problem described by Stephen Bullen
back in 1998, which he seemed to have solved using Application.OnTime
Now, ...: if you don't let the series of events relating to a mouse
click finish before you start messing with the focus and control
states, Excel has a nasty habit of just saying "oh, no you don't!"
after the last event handler for those events, and undoing what you
did. The OnTime trick solved that by setting up an event which waits
for all the pending events to be handled before firing, except:

If you click quickly you can add more pending events before the
previous ones are all handled, and then the OnTime event waits for
_those_ events to be handled also. So, what I need is a way to
"wedge" an event of my own at the end of events which are currently
pending at one instant in time, so that any user actions after that
get queued to be handled _after_ my "special" event.

If I can do that, I've got it made.
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.

I think I may have found a solution: disable the UserForm between
setting up the OnTime event and when it fires. User can't do
anything, so nothing to undo. If that means validation delays user
actions too much, I get a faster computer. IOW, force a single user
action and any validation associated with it to be "atomic" by making
the user wait until both are done.

Somebody noticed a while back that re-enabling the UserForm requires
..Enabled=True to be executed twice; has anyone found out why?

While I was dwelling on the fundamental interconnectedness of all
things on Tue, 15 Aug 2006 22:34:45 GMT, Indiana Epilepsy and Child
Neurology wrote:

While I was dwelling on the fundamental interconnectedness of all
things on Tue, 15 Aug 2006 09:53:01 -0700, Tom Ogilvy
wrote:

if you can't code your conditions within the existing events, then I doubt
you can do it. I don't see any role for an API timer but then I don't know
what you actually want to accomplish.

why can't you call your routine from the appropriate event.

OK, I admit it; I still seek the holy grail of "on the fly validation"
- checking the data as the focus leaves the control, and (sometimes)
forcing the user to go back ahd "do it right this time". I actually
already have a good framework of classes to support this, and it
really works, except for one remaining problem.

Using the existing events causes a problem described by Stephen Bullen
back in 1998, which he seemed to have solved using Application.OnTime
Now, ...: if you don't let the series of events relating to a mouse
click finish before you start messing with the focus and control
states, Excel has a nasty habit of just saying "oh, no you don't!"
after the last event handler for those events, and undoing what you
did. The OnTime trick solved that by setting up an event which waits
for all the pending events to be handled before firing, except:

If you click quickly you can add more pending events before the
previous ones are all handled, and then the OnTime event waits for
_those_ events to be handled also. So, what I need is a way to
"wedge" an event of my own at the end of events which are currently
pending at one instant in time, so that any user actions after that
get queued to be handled _after_ my "special" event.

If I can do that, I've got it made.
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.

OK, here's what I found, for posterity :)

After much experimentation with disabling the UserForm in the Enter
event, I found that to be problematic in that it caused some events
not to be raised for some controls. In particular, the MouseUp event
wouldn't fire for some controls even through MouseDown did!

These problems seemed to go away when I started disabling the UserForm
in the Exit event instead, which makes some sense. I wasn't disabling
it during a series of events related to one control, but rather before
it started, so (it seems) it doesn't interefere. Disabling the
UserForm both ensures that the OnTime event fires right away after the
intrinsic events for a single user activity, but also keeps the user
from getting way ahead of your validation procedure. Re-enabling the
form in the OnTime subroutine seems to make the brief disabling pretty
much invisible to the user.

The skeleton of the solution has become:

In the Exit event, disable the UserForm and queue up the OnTime.

In the Enter event, set a boolean flag that a focus change is taking
place.

In the MouseDown event for some controls (like ListBoxes), save the
original state of the control being clicked on so you can set it back
if validation fails. In MouseDown for all controls, cancel the OnTime
event if one was set up (you might be clicking on a control that
already had the focus, which would not have fired an Exit event, so
there might not be an OnTime queued up-use a boolean flag). If you
don't cancel the OnTime event here, it will fire between MouseDown and
MouseUp. (seems odd to cancel it when you just set it up, but you
didn't know in Exit the mouse was involved).

In the Click handler of the new control (if any-a TextBox doesn't have
one), set a boolean flag that Click did happen (I initialize the flag
to False in the Enter event).

In the MouseUp event, (re-)queue the OnTime.

In the OnTime subroutine, re-enable the UserForm, then proceed with
the validation process, but only if the "focus change" flag was set by
the Enter event. If validation succeeds and the Click flag is set,
you can execute the code (if any) for clicking the new control;
otherwise, set the control back to the state you saved in MouseDown,
then set the focus back to the original control, which you saved a
reference to back in the Exit event.

I also use a boolean flag so that I can turn all this off to prevent
validation while setting the focus back to the original control.

I have tested all the component behaviors this relies on, and it
should work. I've also tested the whole thing in its penultimate
configuration (disabling the form in Enter) and it _almost_ worked.
So, I think there's reason to hope this will work.

While I was dwelling on the fundamental interconnectedness of all
things on Wed, 16 Aug 2006 16:39:47 GMT, Indiana Epilepsy and Child
Neurology wrote:

I think I may have found a solution: disable the UserForm between
setting up the OnTime event and when it fires. User can't do
anything, so nothing to undo. If that means validation delays user
actions too much, I get a faster computer. IOW, force a single user
action and any validation associated with it to be "atomic" by making
the user wait until both are done.

Somebody noticed a while back that re-enabling the UserForm requires
.Enabled=True to be executed twice; has anyone found out why?

While I was dwelling on the fundamental interconnectedness of all
things on Tue, 15 Aug 2006 22:34:45 GMT, Indiana Epilepsy and Child
Neurology wrote:

While I was dwelling on the fundamental interconnectedness of all
things on Tue, 15 Aug 2006 09:53:01 -0700, Tom Ogilvy
wrote:

if you can't code your conditions within the existing events, then I doubt
you can do it. I don't see any role for an API timer but then I don't know
what you actually want to accomplish.

why can't you call your routine from the appropriate event.

OK, I admit it; I still seek the holy grail of "on the fly validation"
- checking the data as the focus leaves the control, and (sometimes)
forcing the user to go back ahd "do it right this time". I actually
already have a good framework of classes to support this, and it
really works, except for one remaining problem.

Using the existing events causes a problem described by Stephen Bullen
back in 1998, which he seemed to have solved using Application.OnTime
Now, ...: if you don't let the series of events relating to a mouse
click finish before you start messing with the focus and control
states, Excel has a nasty habit of just saying "oh, no you don't!"
after the last event handler for those events, and undoing what you
did. The OnTime trick solved that by setting up an event which waits
for all the pending events to be handled before firing, except:

If you click quickly you can add more pending events before the
previous ones are all handled, and then the OnTime event waits for
_those_ events to be handled also. So, what I need is a way to
"wedge" an event of my own at the end of events which are currently
pending at one instant in time, so that any user actions after that
get queued to be handled _after_ my "special" event.

If I can do that, I've got it made.
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.

Final post:

Thanks for the help; with a few minor adjustments, it essentially
works.

<snip
--
Don Stauffer, Office Manager
Indiana Epilepsy and Child Neurology, Inc.