Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Open event does not fire
There have been many threads discussing the problem that sometimes,
mysteriously, the workbook open event does not fire, despite EnableEvents = True. I have encountered this frustrating problem myself on numerous occasions and have concluded it is an Excel bug which, even in the latest version, has not been resolved. I rememeber reading an MSDN article (but cannot now find) recommending using the old Auto_Open subroutine. Sometimes this works, sometimes it doesn't. I have wasted countless hours trying to isolate the problem. Sometimes it appears to be resolved and then it's back again! Aaah! Actually if you have other workbooks open, the problem can propogate to them as well. As soon as you close the offending workbook, all is back to normal! I can't guarantee that the following will work in all circumstances but when I struck the problem again recently, I seemed to resolve it by setting the workbook to manual calculation (via the menu, not VBA). To overcome the irritation of having to constantly press F9, I added this event procedure in the Workbook module: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Application.Calculate End Sub I'd be interested in feedback whether this works for others. The solution suggests that VBA sometimes gets confused when opening a workbook and trying to recalculate at the same time. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Open event does not fire
I've never experienced the problem, but I've seen posts that do this kind of
thing: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a General module, sub Continue_Open() 'your real code here end sub I guess the thought is to let excel do some housekeeping and get caught up. And if you're opening that workbook with the workbook_open code via a macro and that macro is called from a shortcut key---And that shortcut key includes the Shift key, then this can cause trouble. When you hold down the shift key while you're opening a workbook (manually), then excel doesn't run the auto_open or workbook_open code. And the shiftkey in the shortcut key can confuse excel so that it doesn't run the code. Maybe sometimes you'd run the code to open the problem workbook via Tools|macro|macros... and sometimes through a shortcut key??? Robots wrote: There have been many threads discussing the problem that sometimes, mysteriously, the workbook open event does not fire, despite EnableEvents = True. I have encountered this frustrating problem myself on numerous occasions and have concluded it is an Excel bug which, even in the latest version, has not been resolved. I rememeber reading an MSDN article (but cannot now find) recommending using the old Auto_Open subroutine. Sometimes this works, sometimes it doesn't. I have wasted countless hours trying to isolate the problem. Sometimes it appears to be resolved and then it's back again! Aaah! Actually if you have other workbooks open, the problem can propogate to them as well. As soon as you close the offending workbook, all is back to normal! I can't guarantee that the following will work in all circumstances but when I struck the problem again recently, I seemed to resolve it by setting the workbook to manual calculation (via the menu, not VBA). To overcome the irritation of having to constantly press F9, I added this event procedure in the Workbook module: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Application.Calculate End Sub I'd be interested in feedback whether this works for others. The solution suggests that VBA sometimes gets confused when opening a workbook and trying to recalculate at the same time. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Open event does not fire
In the first part of my response, I was guessing that the workbook_open event
did indeed start--but it stopped before it did anything the user could notice. (I don't know what code the OP was using or how he knew that the event didn't fire.) Ian Robinson wrote: I've seen that proposed solution before but it misunderstands the nature of the problem - unless the proponents have a different problem, of course!. Since the Workbook_Open event does not fire (or appear to fire) in the first place, the procedure Sub workbook_Open() ... End Sub is never entered. In other words, it does not matter what code you put inside the Sub (eg merely MsgBox "Hello" or just Stop etc) it will not be run because VBA never gets a chance to. An alternative (and perhaps better) workwround to my previous workaround is to use code like this: Option Explicit Public iCalculationMode As Integer Private Sub Workbook_Activate() iCalculationMode = Application.Calculation Application.Calculation = xlCalculationAutomatic End Sub Private Sub Workbook_Deactivate() Application.Calculation = iCalculationMode End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.Calculation = xlCalculationManual Application.EnableEvents = False ThisWorkbook.Save Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic ThisWorkbook.Saved = True End Sub This method avoids the need to set manual calculation via the menu bar and provides for other workbooks that might be open at the time. At one time I thought the problem was associated with UDFs that may exist and need calculation on startup but the pattern was never consistent. At other times, using Auto_Open worked, but not always (even that would not fire). If you trawl the MSDN Knowledge Base, it appears that Microsoft are aware that there is a problem but never offer a cause or solution other than suggesting that using Auto_Open *might* work. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Event doesn't fire | Excel Discussion (Misc queries) | |||
Workbook Open Event does not fire | Excel Programming | |||
Event class doesn't fire in embedded VBA | Excel Programming | |||
Date/Time Picker Event Won't Fire | Excel Programming | |||
Event to fire when pivottable is pivoted | Excel Programming |