If you suspect there are hidden worksheets in a workbook:
1. Open the VB editor (alt & F11)
2. In the Project Explorer, expand the Project for your workbook
3. Expand the Excel objects for your workbook
Do you see more sheets listed in the Project Explorer than appear in your workbook?
If the answer is Yes, it’s most likely because one or more sheets are hidden.
The following is a macro to unhide all worksheets in a workbook. Right click in the Project Explorer, select insert module, place this code in the module and run it:
Sub UnhideAll()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Visible = True
Next
End Sub
December 14th, 2004 at 8:38 am
You don’t need to add a module for a small, temporary macro like this.
In the immediate window, type this line and press Enter:
For Each WS in Worksheets : WS.Visible = True : Next
Much faster.
December 14th, 2004 at 2:41 pm
Good point Jon.
My stubornness towards the immediate window and msgbox habits have been tough ones to conquer. Thanks.
December 15th, 2004 at 10:50 pm
The code works OK if you can get into the project, but how would you approach the problem if the author had used VBA to set the visible properties of the hidden sheets to “xlVeryHidden” and then locked and passcoded the project? — Just a thought.
December 16th, 2004 at 12:20 am
John,
You make a good point, I made the assumption the workbook was unprotected. I’m unaware of how to accomplish this with a protected project.
December 22nd, 2004 at 4:20 am
I made this, one of thoes thing you start, but never finish: It work ok, but needs more work, which i might do one day.
It was not for opening up xlveryhiden from closed projects but it seemed to work when i tested, might be worth a try?
let me know
December 22nd, 2004 at 8:26 pm
Ross: if you feel like it, post the code and we’ll give it a whirl….
also, one can use a vba “crack” tool if they are desperate to open a locked VBA project (I hear avprp.exe never fails).
December 23rd, 2004 at 5:35 am
Dam it i thought i posted the link - i uploaded the file and all sorts sorry!
http://www.freewebs.com/methodsinexcel/MIE_SheetViewer_Alpha_.zip
December 24th, 2004 at 6:49 pm
Ross,
You have a reference to Rich TextBox Control 6.0 in the add-in that causes an error on my XP/2003 system. No problem though because there is an option to uncheck the reference, and it doesn’t appear the addin uses it anyway because it works perfectly after clearing the reference.
You are 100% correct……….I suppose a this is a universal way to run new code on a VBA protected worksheet; simply place the code you desire to run in another workbook, save as an addin, then import the addin into the protected sheet. Good stuff.
Thanks for the Tip.