Hide/Unhide Tabs VBA

--------------------------------------------------------------------------------

I have Excel report that have 20+ tabs (too many for a single report) this report is intended for several people and these people may just want to see only one tab on the wookbook or all of them, so I think the best solution is to hide the tabs and use several macro/buttons in the first tab (each named for each tab) to see the tabs. When the work book opens all the tabs will be hidden except the Main tab where all buttons are.

The first tab has two buttons to “Show All” and “Hide All” plus several buttons named for each tabs, each tab have a "Hide Tab"-"Main Page" buttons.

The macros (showall/hideall) works fine if all the tabs are visible or hidden, or when I click in a individual name button to unhide a tab. . If I unhide individual tabs with individual buttons and the use the “Show All” button, everything works fine, the rest of the tabs will appear on the work book, but if I use the “Hide All” button when some tabs are visible it gives me a error Run time error; ‘1004 Select Method of Sheets class failed. What I thing is happening is that the macro is looking for all the tabs recorded on the macro to be visible and stops and the macro giving me the error.

What is the best VBA approach to overcome this problem? Can I modify the macro or sheet properties to make it work? Or my only solution is VBA?

Roy, Thanks for the replay. Yes I’m selecting the sheets when recording the macro.

I made a small workbook with 5 sheets and replaced the code for the bottoms (4) with your code, but when made another button for the "Hide All" and record the macro and run it with 2 sheets visible, the same error is popping up; Run time error '1004' Selected Method of Sheet Class Failed.
It does it when some sheets are visible or when all are hiden.
I think I can disable "Hide All" and enable "Show All" after the sheets are hiden to avoid user presing "Hide All" again, but the problem is when some are visible.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I think the more accurate question will be; “How can I hide the all the sheets when some of them are still visible with the “Hide All” button, what will be the best construct to use in the “Hide All”- “Show All” buttons.


Also want to make clear that all buttons are in Sheet1, and this should be always visible, and sheet have names, so no Sheet1, Sheet2 can be use as default.

Hope this is more understandable

Roy, the code work perfect! and eleminated the error too!

Thanks a lot.