Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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/micro...el.programming ....) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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? -- Rick (MVP - Excel) "ras" wrote in message ... 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/micro...el.programming ...) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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... 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. 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.) 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. -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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? -- Rick (MVP - Excel) "ras" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to re-enable events after a VB error in Excel?
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. -- lesco_quartz ------------------------------------------------------------------------ lesco_quartz's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1198 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148822 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Multiple events in the same time | Excel Programming | |||
Help with Enable Macro Error Msg (Excel 2007) | Excel Programming | |||
Enable/Disable CommandBar Buttons Based on Events | Excel Programming | |||
combobox change event is running when enable events is false | Excel Programming | |||
Enable events | Excel Programming |