MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Re-entrant Event handling Sub

  Asked By: Geeske    Date: Mar 25    Category: MS Office    Views: 8911

While not actually correct, that subject line hopefully caught the
attention of someone in the know.

Neophite here, so be gentle with the OO terminology and please excuse
my weird choise of terminology, if the case. I'm still srtuggling
with "method", "property" and the like...

So there's no mistake, I am using VBA in Excel.

Short version:
A CheckBox is greyed out and inactive until the event handling Sub
exits. I want to halt that code by unchecking the box, but it is
grey and inactive. Trying to set various available Button.Properties
=False in the code to allow this didn't work.


I have a check box.
When I click (an event) on it (either to check or unckeck) it fires
some code in the corresponding Macro Sheet (Sheet1 in this case).
That Sub then determines if it is checked or not ( If CheckBox1 Then)
and does the appropriate thing by calling a Sub in one of the other
Modules. This "other code" does its thing, finishes, all the End
Subs cause execution to go the back from whence it came (in true
subrouting RETURN form) and exits the event Sub in Sheet1. This I
have working just fine for many such check boxes, buttons and pull

Here's the rub.

I want to have that "other code" run and run doing something, then
UNCHECK the box to stop that code from running and go back to the
idle state.
However, the box stays greyed-out (disabled in some form) until the
origainal event Sub ends. --- "Obviously" to prevent a re-entrant re-
entry, so to speak.

I can set the check box value (and other properties) to false in
code to change its state, but it is still in-active until that Event-
fired End Sub executes.

In hopes of helping with this, I also tried to find something
like EventSuspend, but no such luck. This is where the event is
captured, but not allowed to fire any code until Events are
resumed... (There's only EventEnable = True/False).

I'm already doing non-overlapped (synchronous, can't do anything till
buffer is sent) serial I/O very successfully (been digging into
MSDN). Sending commands and receiving responses. I want to be able
to sit and wait for serial input, but turn that mode on and off with
a CheckBox. I don't think I can pull off overlapped I/O (Event or
interrupt driven), just yet, so I thought I'd try polling the port,
but I want to stop polling by un-checking the box.



3 Answers Found

Answer #1    Answered By: Duane Walker     Answered On: Mar 25

I am not sure I understand what you are really attempting to do, but it
sounds like in your If, Then, Else statement, you could put in Exit Sub
similar to how you would code  your On GoTo Error Handler (EH) statement.

'Handle Errors Gracefully
Exit Sub

MsgBox Err.Number & ": " & Err.Description
Resume Exit_EH

Answer #2    Answered By: Quinn Johnson     Answered On: Mar 25

Unfortunately I don't understand how error handling  applies since I'm
not generating an error and I can't make a parallel in your code  to
my situation. I'm not to that kind of error handling either, so it
is foreign to me.
However, I found the doEvents function which seems to almost work  for
me. The explanation on MSDN says it can be used to do a premature
Sub exit on an event. Unfortunately, I didn't bring the partially
working code with me

As we know, when you check  a checkBox, this causes an event
which "fires" (jumps to) a subroutine (also an object) in that
sheet. While that sub is executing, it appears that no other events
are recognized. (actually, this sub calls a Sub in another module,
but the results are the same). event  trapping appears to completely
stop. Therefore you can not click  the check box, or any other check
boxes or Command Buttons to do anything. The Sub has to finish
normally before events  are recognized again.
I did find  doEvents which helps, but it is a bit eratic.
To figure out the code, THe event Sub calls a Sub with a While loop
which runs While the check box  value is true. It continually prints
numbers with a debug.Print. The doEvents is in this loop.
Then I try to force it to quit by either "unChecking" the original
check box (thus setting its value to False) or using a command button
to set  the CheckBox.Value to False. Both of these are working, but
about 30% of the time, it takes 2 or 3 clicks on either the box or
the button  to exit the While Loop.

Do you think the event must occur while the doEvents function is
running and that's why it misses some? I was hoping to find a
command to resume event trapping while this sub was running  so I
could manually force it to exit. What I need is a
ResumeEventCapture function. Some way to allow a button or check box
event to "register" on the conciousness of the darn VBA processor and
change that value...while the original, event.caused Sub is running.

Answer #3    Answered By: Kim Coleman     Answered On: Mar 25

DoEvents gives your computer time to process additional commands. I
have used DoEvents when making sure my computer pauses while a second
document is opened.

Please post your code  for the While Loop. It sounds as though it should
be re-written to enable additional processing. I suspect that instead
of a Do While loop, you need an If, then else statement there to handle
the processing the one time and allow your program to continue
processing other commands.

The error handling  was an example of how to exit a subroutine once a
certain condition is met. In the event  of an error, you want to exit
the subroutine. In your case, you want to exit the subroutine if the
value of the checkbox  changes to False.

Didn't find what you were looking for? Find more on Re-entrant Event handling Sub Or get search suggestion and latest updates.