Can't Get This Code to Run in Excel 2007

[CaptRon]

Actually that's not true, the code runs fine IF it's run from the VBE, by pressing F12, from an Add-In toolbar, but not when triggered by the object labeled 'EXIT' on the Start sheet.

I've tried different objects - no good. The code runs then Excel 2007 closes and restarts.

Again, this behavior is only present when the code is run using the object on the Start sheet.

Anybody got any ideas? This Close_Code macro runs without a hitch in Excel 2000-2003.

Ron

[Bob Phillips]

Guess what? Runs fine in my XL2007. And I thought I had one of the flakiest 2007 installations around.

[CaptRon]

Hmmmm? I've tried this on three machines in our office with the same result - click the EXIT button and the macro runs OK, but Excel 2007 shuts down and gives me one of those messages asking if I want to send MS an error report.

Do you think we have a bad installation (done by the same IT group using a clone sort of thing)?

Is there an update or patch or something we are missing? If I run the macro from the VBE, it runs fine and I get no crash and error message. Same if I run it using the custom toolbar.

Ron

[Bob Phillips]

Can't say Ron. I get that error message myself quite frequently, in 2007 and in 2003. I have always assumed that it is either down to something in my installation, not necessarily Excel maybe something that interacts, or soe of my development gets close to the bone.

[david000]

I would do a couple things first:

1. Comment out ALL the error handling code.

2. Eliminate ALL the Call statements.

I would stop trying to hide sheets, protect sheets, make toolbars, etc all at once, because of this sort of problem.

I believe that this line is referring to the active sheet only.
[vba]
ThisWorkbook.Unprotect Password:=Pwd
[/vba]

But, then again it could be a bug!


I'm suspicious that when you set the toolbar that it's not cleaning up after itself very well in XL 2007.

[CaptRon]

What's so odd is it only occurs in Excel 2007, not 2000-2003, I've tried this in all of them. AND it does not occur when I execute the macro from the custom toolbar or through the VBE. Only when executed by clicking on an object associated with it. And I did not observe this behavior (or at least I don't remember it) when I initially tested this full file. The sample I sent was only a piece of the whole.

Here's why I do what I do.... maybe not a great idea, but here's why:

1. Hide all sheets on close and save in that condition to force the user to expand the workbook by clicking a button and unhiding the sheets. This assures that macros are enabled and events are enabled.

2. On this particular workbook, I added code to disable the red X that closes the application, because some of our users would have several of these workbooks open at once, then click on the big red X to close them all at once. Normally that works OK, but with these workbooks, it caused Excel to crash with a message: "Microsoft Office Excel has encountered a problem and needs to close." Close them one at a time, no problem, but 5 at a time - always a problem. Not a great solution as it creates another problem: how to close the workbook.

3. With the regular close methods disabled, I created the close_code macro and connected it to the F12 key, the custom toolbar, an little red button on the Start sheet. Providing my own workbook EXIT method assures that each workbook is closed one at a time.

I'd like to figure out why I can't close five of these workbooks at once, in the first place. I suspected that all the fold and save code was running at the same time creating confusion and conflict with Excel. Or does Excel close each workbook sequentially?

Anyway, thanks for your responses. If you think of anything or could suggest a better approach, I would be very receptive.

Ron