How to re-enable events after a VB error in Excel?

[ras]

I have a VB application in Excel (in Office 2007).
If an error occurs, and the error is caught and err.clear executes,
then all is fine.
If it is not caught, or if I stop the function at this point (with the
Reset button),
events no longer trigger event callbacks!

I added a global function that does: Application.EnableEvents = true
When I run this, in the debugger I see it's already true
and running it does nothing.

To re-enable events I have to quit Excel and restart (losing my
breakpoints).
How do I get events to work again?

thx
(I tried to add this to a similar message, but after submitting it
with "reply to author", didn't see my entry. How do I "reply to
thread"? I'm using http://groups.google.com/group/microsoft.public.excel.programming
....)

 

[Rick Rothstein]

Fast and dirty way to reset events quickly is by executing this line of code
in the Immediate Window...

Application.EnableEvents = True

That will handle your stopping the program manually. As for your "if it is
not caught" statement, what do you mean if it is not caught... how is the
error trap set up that it is missing the error?

 

[ras]

Fast and dirty way to reset events quickly is...in the Immediate Window...

Application.EnableEvents = True

As I said in the original bug, when I execute this in a sub, it's
already true and it doesn't cause events to be triggered.
I'm not sure how to run it in the Immediate Window. I've pasted it in
this window and hit return...

what do you mean if it is not caught?

I just mean if an error occurs where there's no error catching- yes,
this isn't really a problem.
The problem is if I stop debugging instead of letting the code finish
and do the err.clear.
(Running a small sub containing "err.clear" also doesn't get event
callbacks working again.)
thx...

 

[Rick Rothstein]

Fast and dirty way to reset events quickly is...in the Immediate

As I said in the original bug, when I execute this in a sub, it's
already true and it doesn't cause events to be triggered.
I'm not sure how to run it in the Immediate Window. I've pasted it in
this window and hit return...

That should work (as long as the text cursor is still on the line with that
statement in it. You won't see anything visibly, but the next time you run
your code, events should be Enabled again.

I just mean if an error occurs where there's no error catching- yes,
this isn't really a problem.
The problem is if I stop debugging instead of letting the code finish
and do the err.clear.
(Running a small sub containing "err.clear" also doesn't get event
callbacks working again.)

Of course, you should always have an error handler running. If you want to
include the turning on of events coupled with your error clearing macro,
just include this line...

Application.EnableEvents = True

in that macro's code.

 

[ras]

It does NOT work.

Application.EnableEvents = True

This
a) is not disabled by stopping in the middle of running a macro
b) does NOT re-enable events.
If event callbacks stop, I have NO WAY to start them without
restarting Excel.
This is the problem I'm trying to solve.

 

[Rick Rothstein]

Maybe I need to ask what you mean by "event callbacks"... can you describe
what you mean by this and, perhaps, show us some of your non-working code so
we can see exactly how you are trying to proceed?

 

[ras]

Thx. I got it- it "should" work. I guess that means it works for
you...

I have a class module CExcelEvents which starts:

Option Compare Text ' Makes string compare always be case-
insensitve
Option Explicit ' Make code require variable declaration
Private WithEvents XLApp As Application

' Called when the selection changes, eg, by selecting a different
cell on the sheet
Private Sub XLApp_SheetSelectionChange(ByVal sh As Object, ByVal
Target As Range)
dim i as integer
i = 3
end sub

This is called whenever the selection changes, unless I've stopped in
the debugger and quit.
For instance, put a breakpoint at i=3. Select a cell- it stops at
this line. Hit the Reset (square) button.
Select a different cell- the breakpoint is not hit.
Write a sub foo() containing:
Application.EnableEvents = True
Put a breakpoint at the above line. Hit F5. Before the line is
executed, I put the mouse over "EnableEvents" and vis-studio tells me
it's already true.
Hit F5 to continue.
Select a different cell- the original breakpoint is not hit...
Thx

 

[lesco_quartz]

Hi - I had the same problem. Resetting after an error left the
SheetSelectionChange event handling I implemented in an Add In (see
'Application Events' (http://www.cpearson.com/excel/AppEvent.aspx)) not
working.

The work around was to relaunch Excel. Here's how I fixed it ...



In my Add in ThisWorkbook code I have:

Private XLApp As CExcelEvents

Public Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub


I found that if you run this Workbook_Open sub after an error the event
triggering works again. In my vb app I added a menu item that called it
so the user can fix it.

Private Sub ResetEvents()
ThisWorkbook.Workbook_Open
End Sub


Hope this helps.