Hiding Tabs but links to the Hided Sheets/Tab by clicking Buttons linked

[roopesh.chandran]

I have an EXCEL file where I have linked 5 tabs with a button in the Home/Master page by using hyperlink. I want all the users to View only the MASTER PAGE with no TABS. But when they clik on the Buttons(Hyperlinked Shapes) on the Home Page,it should go to that page/tab which i linked.(Simply as a website)Am accepting you as my Excel Guru...

Trying to learn the basics of VB and Macros from your Blog.Please give me some tips to build my requirement in some simple steps.I did unhiding tabs by going into the VB Properties and applied Very hide and Password. But applying that dosent lets me go into the linked TABS when i click the buttons in the home page.

 

[Luke M]

You'll need to change the button so that they link to macros, rather than using hyperlinks. Thankfully, the macros are fairly simple. Here's the example for unhiding one of the tabs, and then another for hiding it and going back to the master sheet. You should be able to copy these as needed for the other buttons/sheets.


Note that the only trick is making sure you unhide a sheet before hiding the last visible sheet, as XL won't let you hide all the sheets! =)

To set up these macros, go to the Visual Basic Editor, then create a module (Insert - Module). Paste these sample codes in, and modify as needed (aka the sheet names). On the buttons on your worksheet, you can right-click and assign a macro to that button. Do this as needed.

[pre]

Sub ShowSheet()
Worksheets("Sample page").Visible = True
Worksheets("MASTER PAGE").Visible = False
End Sub

Sub HideSheet()
Worksheets("MASTER PAGE").Visible = True
Worksheets("Sample page").Visible = xlVeryHidden
End Sub

[/pre]

 

[roopesh.chandran]

Thank you Mr. Luke,, Let me try this.. Is there a way to make my master page with no tab properties visible ..i t should just look like an excel page with no TABS VISIBLE. But tabs exist and should link my butoons to the tabs. On Cliking the button, linked TAB should be opened with no TAB PROPERTIES.


Can i email you my EXCEL project...

if yes please provide me u r ID.

Thanks

 

[SirJB7]

Hi, roopesh.chandran!

Despite of what Luke M might answer, I'd like to share with you my opinion. Please give a look at this topic:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed

Specifically to last post:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903

Regards!

 

[Luke M]

Afraid I tend to agree with SirJB7 on the idea of sharing emails. =/


Options: upload the workbook to a shared site, where everyone can access it. Recommend you dump the data from it. For this problem, shouldn't by a challenge since the only thing is hiding/unhiding sheets.


However, to answer your question directly, I had anticipated this exact requirement when I provided the macros. If you look at them, you'll see that it unhides the desired sheet but then hides the MASTER PAGE sheet, or vice versa, so you should only see one sheet at a time.


I'm not exactly sure what you mean by "no tab properties". Perhaps you could explain this further?

 

[roopesh.chandran]

@ Mr. Luke .. Thanks! Adding more to the TAB Properties.. I dont want any of the user to view my FINAL Project in XL with TAB (To include no colouring tab, renaming etc..) I dont want the tab to be seen at all. Most of the Websites are bloked according to the ORGO policy of my company. So cant upload to a shares site.

 

[Luke M]

If you just want the tabs to be hidden, you can do the folllowing:

2003: Tools - Options - View, Window Options, Uncheck Sheet tab


2007: Excel button, Excel Options - Advanced, Display options for this workbook, uncheck "Show sheet tabs".


Does that work?

 

[SirJB7]

@SirJB7

Hi, myself!

So long...

Just coming back to check for a post containing the requesting user email address and still nothing. Maybe he hasn't one. Or he doesn't know that since middle of '90s they freely available. Or...

Regards!

PS: but as registered at this site he must surely have one, so it'd might be a matter of awareness of free availability, I guess...