hide & unhide sheets in excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nikhil - 23 Feb 2008 18:16 GMT

Hi.. i have a workbook with lots of sheets. I have a main sheet and
other supporting sheets. I want to have a drop down list in the
toolbar (only for this workbook) so that user can select which sheet
he wants to view.

By default only the main sheet should be visible and all others sheets
should be hidden.

When the user selects a particular sheet, that sheet & the main sheet
would become visible. when the user moves out from the particular
sheet, it should automatically become hidden.

I want a link to all the sheets in the main sheet (icons which the
user can click to reach a particular sheet) as also possibly a drop
down in the tool bar with all the sheets listed.

Please help. should this have to be done with a VBA code, request to
please be explicit since i am zero as far as VB goes.

Thanks

Nikhil

Reply to this Message

Nick Hodge - 23 Feb 2008 20:34 GMT

Nikhil

That's not just a job that needs coding, it's a job which is probably beyond
one question with no start point from you.

It would involve RibbonX to add the control to the ribbon and then some VBA
code to action the selection in the combobox. If you want to do this on the
fly, you will also have to capture the ribbon object to invalidate it and
rebuild the control with any changes.

Lastly, you will need event code to hide the sheets when de-activated back
to the 'control' sheet.

You can read the sorts of things you will need to do here (4 parts posted so
far, more to come)

http://excelusergroup.org/blogs/nickhodge/default.aspx

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> Hi.. i have a workbook with lots of sheets. I have a main sheet and
> other supporting sheets. I want to have a drop down list in the
[quoted text clipped - 18 lines]
>
> Nikhil

Reply to this Message