Thread: Orphan Sheets in MS Excel Object folder

Every now and then after creating and removing sheets with VBA code, there are orphan sheets that have no name and have the icon of "ThisWorkbook" object. When selecting them, you see the properties of "ThisWorkbook" object.

They are not in the Sheets() object and I can't erase them.

I attache a pic of what I see. Sheets from 13 to 18 and 20... they are unaccessible from code. I want to delete them.

OrphanSheets.jpg

These sheets Visible property is not set to xlSheetVeryHidden? Youu cannot unhide them from the worksheets? Can you post a workbook with an example?

Hi,
Do the sheets disappear when you close and reopen the workbook? If so, do you have any COM addins loaded?

Here is the file. I tryed to make it smaller by erasing the code and the data but I couldn't.
They don't disapear when I close and reopen the file.

You will see that when you double click them they have code in them.

There are like 5 base sheets. When I want to load a new case, when I hit the "New case" button, VBA would unhide NewCase Sheet, duplicate it and then Hide it again.

When I loaded all the fields in the case and I hit save and close, VBA loads the case in Database sheet and then deletes this duplicated NewCase Sheet...

Database Project - Copy.xlsm

I think you workbook is corrupt. I think to try and fix this would be almost impossible - I changed the name of ThisWorkbook to e.g. acv and then all the sheets marked as workbooks became named acv. My advice would be to take all the code out of thisworkbook and place it into modules and only keep worksheet event code in worksheets and thisworkbook code in this workbook and remove all the code for your buttons into modules. How this has occurred I have no idea - I have read a few posts from people who have reported a similar problem (they are few in number) and the final result is to start a new workbook and copy out your sheets and redesign so only worksheet and thisworkbook code is confined to them respectively and place all the button code into modules.
As to how to replicate the problem I have no idea however somewhere along the line the workbook has been corrupted and the fastest way to resolve this is to start a new workbook and redesign where you place your code. Best answer I have.
Will be interested if someone else can shed more light on this.
PS: I am intrigued as to how ThisWorkbook code has been split between different orphan sheets - it is not even the same code in each orphan. Very weird.

I have not had time to examine the structure of the XML file but the first thing I notice in the code is that you are using ADO to query the workbook while it is open. That is not recommended due to known memory leak issues and may be the root cause (or one of them) of your issues.