Deleting tabs has a tendency to make Excel crash

There are a couple spots in the workbook I built where I want to delete one or more tabs. The first scenario I used was an extremely complicated one where the delete command occurred at the end of a large amount of code, so I assumed that was the reason Excel got confused and crashed.

However, I am also seeing this in a much simpler scenario. Essentially I have two tabs in my workbook which are used for demo purposes only (to introduce the workbook functionality). When I'm done, I want to click a button that says "End Demo" which will then delete the two demo tabs. I have researched this extensively and tried implementing some of the solutions I've discovered, but none have helped. Here is the code the button calls:

Please Login or Register  to view this content.

I cut the button that called the code so it didn't get hung up on trying to call that object on a deleted tab. I introduced a brief wait to allow the system to catch up. And I activated a different workbook tab so it wasn't deleting the active sheet. Still I get the crash more often than not. What else can I try to allow these tabs to delete without crashing?

See attached

I think your problem is that the Button11_click event is in one of the worksheets you are deleting. If you move the button to the first worksheet that you are not going to delete, it works fine.

Originally Posted by EssoExplJoe

I think your problem is that the Button11_click event is in one of the worksheets you are deleting. If you move the button to the first worksheet that you are not going to delete, it works fine.

Unfortunately I need the button to be on the page that is being deleted. This is true in the other example I alluded to in my first post. That's why I was trying to cut the button object before deleting. Do you have any other workarounds?

You might be able to have the button11 trigger an event on the main sheet then delete the sheets from there. For example, you could set a cell (maybe A1) to a code like 99999. In the main sheet cell change event, delete the two sheets if the target in the sheet change event was cell a1 and the value 99999. Then reset cell A1 to whatever. This might do it.

Originally Posted by xlnitwit

I cannot reproduce the crash in in 2010 but would suggest that you use OnTime in order to allow the button click code to finish before deleting the button or its sheet

Please Login or Register  to view this content.

This worked perfectly, thanks so much!