Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
I've encountered the following problem that I wonder if anybody else has
encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
Are you talking application events? IS the code in the first workbook being
closed perhaps? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Josh Sale" <jsale@tril dot cod wrote in message ... I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
Josh, I just tried this and it worked fine for me. I was not using and
add-in, I just created three workbooks and put Workbook_BeforeClose code in each. Mark Josh Sale wrote: I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
No, its not an application event. Each workbook has Workbook_BeforeClose in
its ThisWorkbook module. Each event handler has just a single line that makes a call into the add-in. "Bob Phillips" wrote in message ... Are you talking application events? IS the code in the first workbook being closed perhaps? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Josh Sale" <jsale@tril dot cod wrote in message ... I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
I repeated your experiment placing a Workbook_BeforeClose event handler into
each workbook. Each event handler had a single statement: Msgbox Activeworkbook.Name As you suggested, in this case the event is raised twice (once for each workbook). Unfortunately, the same workbook is active for both events. So there is no way to tell what workbook the event is being raised for! josh "Mark Driscol" wrote in message ups.com... Josh, I just tried this and it worked fine for me. I was not using and add-in, I just created three workbooks and put Workbook_BeforeClose code in each. Mark Josh Sale wrote: I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
Josh, I used
MsgBox Me.Name and it told me what Workbook the event was for. Mark Josh Sale wrote: I repeated your experiment placing a Workbook_BeforeClose event handler into each workbook. Each event handler had a single statement: Msgbox Activeworkbook.Name As you suggested, in this case the event is raised twice (once for each workbook). Unfortunately, the same workbook is active for both events. So there is no way to tell what workbook the event is being raised for! josh "Mark Driscol" wrote in message ups.com... Josh, I just tried this and it worked fine for me. I was not using and add-in, I just created three workbooks and put Workbook_BeforeClose code in each. Mark Josh Sale wrote: I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close All and Workbook_BeforeClose events
Great tip Mark!!!
Thanks. "Mark Driscol" wrote in message ups.com... Josh, I used MsgBox Me.Name and it told me what Workbook the event was for. Mark Josh Sale wrote: I repeated your experiment placing a Workbook_BeforeClose event handler into each workbook. Each event handler had a single statement: Msgbox Activeworkbook.Name As you suggested, in this case the event is raised twice (once for each workbook). Unfortunately, the same workbook is active for both events. So there is no way to tell what workbook the event is being raised for! josh "Mark Driscol" wrote in message ups.com... Josh, I just tried this and it worked fine for me. I was not using and add-in, I just created three workbooks and put Workbook_BeforeClose code in each. Mark Josh Sale wrote: I've encountered the following problem that I wonder if anybody else has encountered. I have an application that is implemented as an Excel add-in. Each workbook that contains application data contains a Workbook_BeforeClose event handler that calls code in the add-in. If the user closes the workbook in any of the typical ways, the event is raised and life is good. However, if a user has two or more application workbooks open and then does a File | Close All (accessed by holding down the Shift key when clicking the File menu item) then the Workbook_BeforeClose event is only raised for the active workbook ... even though all of the workbooks are being closed. As a result, my application doesn't have a chance to cleanup after all of the workbooks being closed. I would have thought/hoped that Workbook_BeforeClose would be called for each successive workbook (first making that workbook active since the event doesn't provide the workbook as an argument). Is anybody else surprised by this behavior? Anybody got any good workarounds? TIA, josh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook_beforeClose | Excel Programming | |||
Help on Workbook close and workbook save events | Excel Programming | |||
Workbook_BeforeClose | Excel Programming | |||
On close and before save events. | Excel Programming | |||
Events for workbook close | Excel Programming |