Close Excel workbook without triggering changes events

[MatthewGB]

Hi,

I have a excel workbook with a number of controls (cmdButtons, textboxes etc) that have code attached to their _Change events. Whenever I close the workbook, the code in these modules is triggered. What would be the best way to prevent this from happening?

By the way, there is no Workbook_BeforeClose code in place so this should not be causing the problem.

Thanks

Matt

 

[xlbo]

Application.enableevents = false ???

watch it tho - if not set back to true, events will not be re-enabled until excel is closed and re-opened Rgds
Geoff

[blue]Si hoc legere scis, nimis eruditionis habes[/blue]

 

[RobBroekhuis]

Not very satisfying - but you should be able to add a _beforeclose event that sets a global variable (e.g. Closing) to true. Then in each _change event you can do

if Closing then exit sub

I'm puzzled that this would be required, though. Why would a commandbutton_change event trigger upon closing?
Rob

 

[xlbo]

Are there any worksheet change / calculation events - these can be set off at closing if you have calculate before save ticked in options or even if you have a lot of formulae - it can cause a recalc at saving Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[MatthewGB]

Hi Geoff and Rob,
Sorry about the late reply, but I haven't had much time to work on my project recently.
Anyway as for Geoff's last question, there are no worksheet change or calculate events, only a few combobox_change events. Plus, there is only minimal formulae in the cells of the worksheets. There is however an add-in referenced which contains an interpolated vlookup function. This is used on two of my sheets, but I don't see how this would be triggering the combo change event.

Rob, I tried your solution but didn't seem to have any luck with it. The workbook still seems to take ages to shut down, which may or may not be related to the change events being triggered.

Thanks

Matt

 

[RobBroekhuis]

You could put in some debugging code in your _change subs to write to the immediate window, and then close the workbook from the immediate window. That way, you can judge which part of closing down is taking so long. If it's the change event handlers, then it's worth spending some more time investigating those. If not, then you'll have to look elsewhere...
Rob

 

[MatthewGB]

Rob,

I have taken the debugging a few steps further by removing pieces of code one at a time to determine what is causing the problem. The bizarre thing is, I have removed ALL code and the extremely long closing time is still occuring. (By the way, opening the excel file takes a long time also).

I going to try removing everything (forms, buttons, even plain spreadsheet values, named ranges) to try and get the spreadsheet opening and closing properly.

If you have any ideas as to why this is occuring (for example, my referenced dll's or whatever may be screwy),
I'd appreciate your thoughts.

 

[MatthewGB]

I have managed to track down what is causing the massive delays. The delay disappears when I delete all cmdbuttons, labels, textboxes etc on the worksheets (note, the buttons on the userforms don't seem to affect the closing time).

These buttons were created using the control toolbox. I think that the buttons used from the forms toolbox work alright. Do the forms toolbox controls function the same as the control toolbox controls, (ie. are they reference the same way, same functionality etc)?

This problem suggests to me that their is a problem with the Object library which the control toolbox utilises. I am running Excel 2000. Does anyone know what the correct libraries is should be using (eg filename, filesize), so i can check if mine are corrupted?

Thanks in advance

Matt

 

[MatthewGB]

A little more information regarding the above problem. I have counted the number of OLEObjects (controls) on the worksheets, and the opening time and closing time seems to be consistently 2 secs for each OLEObject

i.e. If my workbook contains a total of 12 OLEObjects on its 8 worksheets, then the opening time would be always around 24 secs. If 20 objects, then 40secs. I am stumped by this bizarre behaviour as its has only recently occurred (about 1 week or so ago).

Before Christmas I didn't have this problem, but i'm clueless as to what i have done to my spreadsheet to bring on this problem.

 

[xlbo]

I doubt this will help immensley but I've never had a problem with OLE Objects (ie controls toolbar objects) within a form. I do, however, tend to avoid them for worksheets and use Forms toolbar buttons instead. To answer your question regarding the differences between Forms toolbar objects and Controls toolbar objects - no they do not function the same way. Forms toolbar objects are a leftover from excel 95 and do not have nearly as many features as Controls toolbar objects. They have basic formatting and the ability to attach code to the click event as opposed to controls toolbar objects which have a whole range of events open to them and far more properties. Within a worksheet, I have rarely found a reason to use controls toolbar objects as most of the time the necessary functionality can be controlled by forms toolbar objects, worksheet events and calculation. The syntax for referencing them differs also as they are part of the SHAPES collection rather than a collection of objects in their own right

HTH Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes