CODEWITHSUNDEEP
vbaexcelcrashruntime-error
ARich
ARich

Reputation: 3279

Excel Crashes Upon Editing Script in ThisWorkbook

I have an Excel 2010 file (.xlsm) that contains code in a few modules including ThisWorkbook. The ThisWorkbook module contains a few Event procedures, including a _SheetActivate event.

I have a button on one sheet that, when clicked, activates a different sheet.

Today after working on some code in one of the regular modules, I clicked the button and got a 

Run time error '-2147417848 (80010108)' 
Method 'Activate' of object '_Worksheet' failed.

Any attempt to activate a different sheet (manually or programmatically) crashes the Application.

After some extensive research, all I found was this page which gives further information regarding the error: 

-2147417848 (80010108): The object invoked has disconnected from its clients.

Resolving the error isn't so much of a concern to me because I don't need that particular event anymore. However, I do need to figure out why Excel keeps crashing. I tried to delete the code, but Excel crashed. So I tried commenting the code out, but Excel still crashed. Alas, everything I've tried has resulted in a crash. This leads me to believe that something must be corrupt in the script within that module (the code in other modules seems to work fine, but none of the other procedures activate a sheet).

I've read that an option would be to copy everything (including code) over to a new workbook, but I'd like to avoid that if possible as that would require copying more than the current sheet...but I can't copy the other sheets without Excel crashing. Does anyone have any ideas on how I could remove/delete the code from ThisWorkbook?

Upvotes: 0

Views: 1528

Answers (1)

ARich
ARich

Reputation: 3279

After too many crashes to count, I was finally able to resolve the issue.

First I copied the code (Ctrl + C) from ThisWorkbook that I wanted to be able to use later and pasted it into a blank notebook file. Then I did as Patrick suggested and exported the regular modules.

At this point, I tried programmatically removing the code, but Excel crashed again. Then it dawned on me that I didn't have to delete the code manually or programmatically; I just saved the file as an .xlsx document which by design removes the code.

From there I closed the file, opened it back up, and imported the .bas files I'd exported earlier. The last step before I saved the file in .xlsm format was to paste the copied code from the first step back into ThisWorkbook.

Everything works like a charm now. Thanks for the help! Cheers!

Upvotes: 2

Related Questions