Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open event disabled by manual calc command
I am using Excel 2003 on Windows XP professional.
I have some code that runs every time a workbook is opened. As you would expect, it makes no difference to the firing of this event whether the application is set to manual or automatic calculation mode. However, when I change from automatic to manual calculation using the following line of code in an unrelated procedure, the Workbook_open event handler stops working until I close and re-load Excel. Application.Calculation = xlCalculationManual Any ideas why this is and what to do about it? TIA Anthony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open event disabled by manual calc command
I can't reproduce your problem. Are you sure that the procedure which
changes the application.calculation value doesn't also change Application.EnableEvents? Maybe you can do a project-wide search for occurences of "Application.EnableEvents" Is the problem is showing up when you invoke Workbooks.Open() to open a workbook with a workbook_open event-handler? Maybe before the offending line you can put the line msgbox Application.EnableEvents prior to the Open() command to see if the EnableEvents setting has somehow been changed. If it has been and for some reason you can't track down just where it was changed a work-around might be to use the 2 lines ApplicationEnableEvents = True Workbooks.Open("myfile.xls") Finally, in the workbook that you are trying to open, have you tried putting something like msgbox "I'm here!" at the very start of the workbook_open() sub? It is possible that execution is reaching there but that your other code has changed something else (other than the calculation mode) causing the open event to fire but to not work as usual. If you have some sort of error-trapping it is possible that this could go unnoticed. Just a few stabs in the dark. I'm sure you've tried similar things. -John Coleman On Mar 12, 9:58 am, Twotone wrote: I am using Excel 2003 on Windows XP professional. I have some code that runs every time a workbook is opened. As you would expect, it makes no difference to the firing of this event whether the application is set to manual or automatic calculation mode. However, when I change from automatic to manual calculation using the following line of code in an unrelated procedure, the Workbook_open event handler stops working until I close and re-load Excel. Application.Calculation = xlCalculationManual Any ideas why this is and what to do about it? TIA Anthony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open event disabled by manual calc command
Thanks for the suggestions. To be clear, I need my event to run every time
the user manually opens any workbook they happen to have access to (The procedure then stores information about each workbook that is opened). I am therefore not using Workbook.Open(). I have checked that EnableEvents is True and remains True (a) when my procedure that includes "Application.Calculation = xlCalculationManual" starts and ends; and (b) when the event-handler starts and ends. I think when an event like Workbook_Open occurs, there are actually several "things" that occur and they occur in a particular order. I can't remember what they are but am wondering if it is one of those that is being affected and stopping the event handler from working. Does this ring any bells? TIA Anthony "John Coleman" wrote: I can't reproduce your problem. Are you sure that the procedure which changes the application.calculation value doesn't also change Application.EnableEvents? Maybe you can do a project-wide search for occurences of "Application.EnableEvents" Is the problem is showing up when you invoke Workbooks.Open() to open a workbook with a workbook_open event-handler? Maybe before the offending line you can put the line msgbox Application.EnableEvents prior to the Open() command to see if the EnableEvents setting has somehow been changed. If it has been and for some reason you can't track down just where it was changed a work-around might be to use the 2 lines ApplicationEnableEvents = True Workbooks.Open("myfile.xls") Finally, in the workbook that you are trying to open, have you tried putting something like msgbox "I'm here!" at the very start of the workbook_open() sub? It is possible that execution is reaching there but that your other code has changed something else (other than the calculation mode) causing the open event to fire but to not work as usual. If you have some sort of error-trapping it is possible that this could go unnoticed. Just a few stabs in the dark. I'm sure you've tried similar things. -John Coleman On Mar 12, 9:58 am, Twotone wrote: I am using Excel 2003 on Windows XP professional. I have some code that runs every time a workbook is opened. As you would expect, it makes no difference to the firing of this event whether the application is set to manual or automatic calculation mode. However, when I change from automatic to manual calculation using the following line of code in an unrelated procedure, the Workbook_open event handler stops working until I close and re-load Excel. Application.Calculation = xlCalculationManual Any ideas why this is and what to do about it? TIA Anthony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open event disabled by manual calc command
Sounds like you are wanting "Application level events." With ordinary
events, there is no reason for code in workbook A to fire automatically when workbook B is opened - but if you have enabled application-level events in A, code in workbook A (assuming it is open of course) *can* fire automatically when any other workbook is opened. I have never used such events, but, as in many things, Chip Pearson's website is a good source of information: http://www.cpearson.com/excel/AppEvent.htm (you can also look at the online help of course) Hth -John Coleman On Mar 12, 1:30 pm, Twotone wrote: Thanks for the suggestions. To be clear, I need my event to run every time the user manually opens any workbook they happen to have access to (The procedure then stores information about each workbook that is opened). I am therefore not using Workbook.Open(). I have checked that EnableEvents is True and remains True (a) when my procedure that includes "Application.Calculation = xlCalculationManual" starts and ends; and (b) when the event-handler starts and ends. I think when an event like Workbook_Open occurs, there are actually several "things" that occur and they occur in a particular order. I can't remember what they are but am wondering if it is one of those that is being affected and stopping the event handler from working. Does this ring any bells? TIA Anthony "John Coleman" wrote: I can't reproduce your problem. Are you sure that the procedure which changes the application.calculation value doesn't also change Application.EnableEvents? Maybe you can do a project-wide search for occurences of "Application.EnableEvents" Is the problem is showing up when you invoke Workbooks.Open() to open a workbook with a workbook_open event-handler? Maybe before the offending line you can put the line msgbox Application.EnableEvents prior to the Open() command to see if the EnableEvents setting has somehow been changed. If it has been and for some reason you can't track down just where it was changed a work-around might be to use the 2 lines ApplicationEnableEvents = True Workbooks.Open("myfile.xls") Finally, in the workbook that you are trying to open, have you tried putting something like msgbox "I'm here!" at the very start of the workbook_open() sub? It is possible that execution is reaching there but that your other code has changed something else (other than the calculation mode) causing the open event to fire but to not work as usual. If you have some sort of error-trapping it is possible that this could go unnoticed. Just a few stabs in the dark. I'm sure you've tried similar things. -John Coleman On Mar 12, 9:58 am, Twotone wrote: I am using Excel 2003 on Windows XP professional. I have some code that runs every time a workbook is opened. As you would expect, it makes no difference to the firing of this event whether the application is set to manual or automatic calculation mode. However, when I change from automatic to manual calculation using the following line of code in an unrelated procedure, the Workbook_open event handler stops working until I close and re-load Excel. Application.Calculation = xlCalculationManual Any ideas why this is and what to do about it? TIA Anthony- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc = Manual & Do Not Calc b/4 SAVE | Excel Discussion (Misc queries) | |||
Open CSV causes calculation in manual calc mode | Excel Discussion (Misc queries) | |||
Automatic Manual Calc When Opening!! | Excel Programming | |||
Switching calculation to manual using Workbook_open | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |