VBA execution exits after call to Worksheet.Delete. No error, no debug prompt, just quits

The attached file is a tool to create weekly project reports from an external data source. (I have deleted everything except what is needed to demonstrate the problem.) Some sheets are temporary and are deleted and re-created each time the tool is run. These temporary sheets have tabs with no color or black color. Sheets having tabs with other colors are part of the baseline tool and should never be deleted.

There is a button on the Control sheet to invoke a sub that deletes all of the temporary sheets. The button executes sub ResetSheets in module MainCommon. This Sub has a loop to check every sheet, and if it is not a baseline sheet, it deletes it.

The module Exclude has a function StaticSheet that takes a sheet name and returns a Boolean TRUE iff the sheet is a baseline sheet and should not be deleted.

The problem is that when this code finds a sheet to delete, it successfully deletes it, but then execution stops after the first deletion. There is no error, no debug prompt--it just stops execution as if it were a normal exit. If I repeat execution, it will move on to delete the next sheet in line, but then once again stops with no error. When I step through the code in the debugger, the behavior is the same: the Delete line is executed then execution just stops. It does not give an error nor move on to the next line of code.

Please Login or Register  to view this content.

I'll try your suggestion, but

Originally Posted by Kyle123

Deleting items from a collection you are iterating through isn’t a good idea.

why? If it causes an invalid condition (e.g., the For loop tries to advance to the next object in the collection but I've already deleted it), it should result in an error or bug, but I would not expect it to cause a halt to execution in the middle of a sub.