Forum Discussion
Remove "Ghost" Excel sheet object from VBA
Hello,
I am unable to remove these "Ghost" excel objects left behind after deleting the sheet.
I have tried multiple methods that I found on the internet to remove these objects, but nothing works.
The sheets are not hidden. The sheets do not existing. That is the "hard coded" name created when the file is created from copying the "CAD" or "CAD_Template". The "remove" button is grayed out. I tried basically everything CoPilot could tell me to remove this.
This is what it looks like below. When I run the program, the program creates "CAD4" as the next hard coded name.
After trying many methods that did not work from CoPilot and other AI engines, the easiest method was to export all the modules, userforms, and sheets to remove these "Ghost objects". There appears to be a more time consuming and complicated method to manually edit the binary code directly in the VBA project structure.
It appears that it is not "corrupt" per se but some how the codename (CAD2) is somewhere in the project structure.
I was successful exporting all the modules, userforms and sheets and then transferred all to a new Excel file to save. The ghost files are gone. Not everything transfers. I had some "buttons" that referenced macros that looked for the previous version name, so you have to be careful to name the project the same name or edit those buttons references macros.
3 Replies
The following procedure is more detailed than HansVogelaar's. It will remove ghost document modules (like Sheet11 and ThisWorkbook1) from a macro enabled workbook (.xlsm or .xlsb):
● In the VB Editor, open Tools > References and take a screenshot.
● Export and remove all VBA form, class, and standard modules.
● Save the workbook as .xlsx (not macro enabled), then close Excel.
● Reopen the .xlsx file, which should have no remaining ghost modules.
● Import VBA form, class, and standard modules previously exported.
● Use Tools > References to restore necessary references.
● Save as macro enabled (.xlsm).
● For each remaining ghost-like Sheet module (like Sheet111…):
– Activate the old sheet, press Ctrl+A, and press Ctrl+C to copy all.
– Insert a new sheet and press Ctrl+V to paste all from the old sheet.
– Record the old sheet’s name, then delete the old sheet.
– Rename the new sheet with the old sheet’s name.
● Resave the .xlsm file, which should have no remaining ghost-like Sheet modules.
● Carefully test the result.Note that the icon of the ghost sheets is that of the ThisWorkbook object. This is usually a sign that the workbook is corrupt.
Possible solution:
- Export all VBA code.
- Save the workbook as a .xlsx workbook (this will remove all VBA code).
- Import the VBA code.
- Save as a .xlsm workbook.
Or:
- Copy all real worksheets and all modules to a new workbook.
After trying many methods that did not work from CoPilot and other AI engines, the easiest method was to export all the modules, userforms, and sheets to remove these "Ghost objects". There appears to be a more time consuming and complicated method to manually edit the binary code directly in the VBA project structure.
It appears that it is not "corrupt" per se but some how the codename (CAD2) is somewhere in the project structure.
I was successful exporting all the modules, userforms and sheets and then transferred all to a new Excel file to save. The ghost files are gone. Not everything transfers. I had some "buttons" that referenced macros that looked for the previous version name, so you have to be careful to name the project the same name or edit those buttons references macros.
