Excel shutting down during save

[jomili]

Okay, I've got a strange problem. The macro below WAS (yesterday) working well to save the active workbook to a specific location with a specific name. Now, Excel shuts down when it hits that bit of code. Any idea what would be causing it? BTW, it tries to save to the location; there's an unknown type of fill deposited there, with a name like "7A08C000". If I put an XLS extension on it it says the file format is not valid.

If I change "ActiveWorkbook" to "TwoB" I get the same result. But, if I make a different workbook the active workbook, the macro works.

Please Login or Register  to view this content.

EDIT: I edited the post to attach a copy of the Lookups sheet.

[jomili]

Okay, I've done some more testing, and I can narrow down the problem even more.

The problem seems to be triggered by the HO and MO variants; for some reason Excel doesn't seem happy about those two. Any ideas on another way to do it?

[jomili]

I still need help.

I tried this morning just saving the active workbook with a new name.

Please Login or Register  to view this content.

Same reaction, Excel shuts down and offers to recover my work.

[romperstomper]

Two thoughts spring to mind:
1. Use HO.Value and MO.Value
2. Don't call your sub Save as that is an Excel method

[jomili]

Romper,

Thanks for the advice, but no go. I tried putting .value after MO and HO, and still got the same reaction. "Save" was just a temporary macro, not the full routine, so I changed the name to "SaveIt". As a separate routine, if I just run it over and over, sometimes it will work, sometimes it won't. As part of the larger routine, it fails every time, but catastrophically, by shutting down Excel. I've pasted the larger routine below; it's a lot to go through, but maybe it will give you a clue as to what's happening.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[romperstomper]

A couple of immediate questions/observations:
1. Why are you using Run rather than calling the code directly?
2. There's a lot of unnecessary selecting, activating and unqualified Range statements in there.
3. What are the values of HO and MO?

[jomili]

1) Self-taught in VBA, so blame my teacher that I don't know the right code to use. What do you mean by "Call directly", rather than "Run"?

2) You'd have to point out specifics for me to explain the "why"s of these.

3)

Please Login or Register  to view this content.

TwoB is the workbook that contains the Lookups sheet I attached to my first post. HO and MO are in there.

[romperstomper]

1. I mean:

Please Login or Register  to view this content.

rather than:

Please Login or Register  to view this content.

2. For example here:

Please Login or Register  to view this content.

you would be better off explicitly stating which sheet is being used. The odd thing is that sometimes you do it and others you don't - such as in this part:

Please Login or Register  to view this content.

where you have two fully qualified Range references, one unqualified one, then one qualified only with Activesheet.

3. That doesn't tell me what the values are.

[jomili]

Run or Call;
I just did some research, and it appears you use Run when you're calling a macro from another workbook, Call when you're running a macro within a workbook. All of my macros are run from one workbook, and they all modify another workbook, and use other workbooks. So that's why I use Run.

[romperstomper]

If the code is all in the same workbook, you don't use Run, regardless of which workbook is being modified. If you do use Run, you should specify the workbook that the code is in.

[jomili]

For 1), are you saying I could just put Killbutts, with nothing else, and "Killbutts()" would run?

For 2, you're right. I had thought

Please Login or Register  to view this content.

would cover all of the code under it until my "End With", but my WITH is only talking about A1, not the other cells. Sloppy of me; I'll work on that.

For 3, I don't remember what values were in the workbook I first posted, as they change depending on what month we're in a what region we're running. Representative values are June for H1, 030 for M1.

[romperstomper]

1. Yes. You can also use

Please Login or Register  to view this content.

but it is unnecessary.

Do you have any code in the BeforeSave event as a matter of interest?

[jomili]

No, nothing before save. Would it help if I posted the workbook that all of my code is in? A lot of it wouldn't run, as it depends on other workbooks, but you're welcome to inspect the code if you'd like.

[romperstomper]

Feel free - though if it's a lot of code I can't guarantee when I can look through it.

[jomili]

It's getting worse. When my active workbook (TwoB) is created, it's automatically saved to my desktop. So, I added a routine right before my attempted save to the new drive, to save the active workbook, no name change or anything. Excel crashed again, no error message other than Excel is attempting to save my work. The code I used to save my workbook is simply

Please Login or Register  to view this content.

. I use it successfully in previous macros, but at the point in question it causes Excel to crash.

I've attached my workbook containing all of my code in the hope that someone can pinpoint the problem. As it stands now, ActiveWorkbook.Save works in the "Lookups" macro, but fails in the "Finish" macro. Any help is greatly appreciated.

[jomili]

Okay, I've narrowed it down more. ActiveWorkbook.Save works BEFORE "Killbutts", but ActiveWorkbook.Save immediately after "Killbutts" causes Excel to fail. So, I'm thinking "Killbutts" might be the problem, I just don't know why. If I remove Killbutts, it runs flawlessly.

Here's "Killbutts"; it's purpose is to delete some autoshape buttons on the active sheet. It seems to work well, but something's going wonky with it.

Please Login or Register  to view this content.

BTW, Killbutts grew from this post: http://www.excelforum.com/excel-prog...-coloring.html

[romperstomper]

My initial impression is that your workbook is corrupt as it keeps leaving shadow copies of itself open in the VBE (though it doesn't crash when I save it). My earlier points still apply though, and you ought to use Thisworkbook to refer to the workbook running the code, rather than hardcoding its name into the code.

[romperstomper]

Comment out the shape delete, then test it again.

[jomili]

Running it like this, everything works fine (except my buttons aren't deleted):

Please Login or Register  to view this content.

[romperstomper]

How about if you use:

Please Login or Register  to view this content.

[jomili]

That works, no Excel failure. It deletes my two buttons, but it doesn't delete the two red rectangular autoshapes I had behind my two buttons.

[romperstomper]

Try replacing the loop with:

Please Login or Register  to view this content.

and see if that does it. It's generally not a good idea to just delete all shapes on a sheet, especially if you have autofilters, data validation and the like on it.

[jomili]

Romper, thanks for the advice on deleting shapes. I'm not at all familiar with shapes, so this stuff is all new to me.

I changed Killbutts as you suggested, and it's doing the trick now with no hiccups. Thank you very much for helping me locate and fix the problem. I'll see if I can implement all your suggestions for tightening up my code.

Thanks,
John

Please Login or Register  to view this content.

[romperstomper]

Glad to help. All good fun, isn't it?

[jomili]

Sure. Except for all my hair falling out, a nervous giggle whenever I speak, and the twitching of the whole left side of my face, I'm having the time of my life!

[romperstomper]

Only the left side? Give it time.

Oh, and please don't forget to mark Solved. Ta.