Remove sheet tab

**justinng** · 03-20-2010, 03:44 PM

Hi there,

Can anyone tell me how to prevent user from restoring the sheet tab under the tools, option, view? Anyway to hide the sheet tab within the excel funtion or macro??

Many thanks

**royUK** · 03-20-2010, 03:48 PM

Do you mean that you have a sheet that you don't want the user to access?

**tony h** · 03-20-2010, 03:52 PM

Which version of Excel.

If there are sheets you don't want people to see then you can set the sheet visible property to xlVeryHidden. This needs acces to the VBA module to undo it. The VBA module can be password protected.

**justinng** · 03-23-2010, 11:28 AM

Hi there,

Thanks for your reply

I would like to hide the sheet tab so that the user is not able to see the sheet tab or switching between the sheet by choosing the sheet tab. I would like the user to click on the hyperlink that I have linked to switch between the sheets.

If I put the sheet as "very hidden", the formulae that point to that sheet will not work.

Any suggestion?

**dominicb** · 03-23-2010, 12:41 PM

Good afternoon justinng

Go to Tools > Options, View and untick the Sheet Tabs button and click OK. This will remove from view your sheet tabs. Then your users will have to use your hyperlinks to navigate.

The VBA code for doing this would be :

Code:

ActiveWindow.DisplayWorkbookTabs = False

HTH

DominicB

**justinng** · 04-03-2010, 02:43 PM

Hi Dominic,

Even if I untick the option, the user will still be able to go option and tick to show the sheet tab on their own.

Anymore suggestion?

Thank you

**tony h** · 04-03-2010, 04:31 PM

if you use the "very hidden" option you can code the navigation to "un hide" any sheet as you need to and to check the sheet tabs are still hidden.

**justinng** · 04-05-2010, 11:44 AM

Hi Tony,

Thanks for the suggestion, but if I set the sheet as very hidden, the hyperlink that linked to that 'very hidden' sheet will not be working.

What can I do?

**dominicb** · 04-05-2010, 12:08 PM

Hi justinng

What you want will need macros. You will have to ditch the hyperlink idea and go for a series of buttons. Each button will have to unhide the requested sheet first, then hide the menu sheet. Each worksheet will need it's own navigation buttons to return to another sheet, or the menu.

The whole thing is going to be extremely messy and a nightmare when you add new sheets. Also, users may well be able to get into the macros and tinker with it from there.

You could use the code I gave you within a Worksheet Change event, so that if anyone were to switch the tabs back on then they would be hidden again the next time the worksheet changed. The flip side of this is that you would lose your undo facility.

You are going to have to accept some kind of compromise that you can't have exactly what it is that you're after. But there is no sign of any compromise.

DominicB

**PetMice** · 04-05-2010, 12:26 PM

In my ancient Excel 97, assuming you don't want to work on the sheet too often, I would rename the sheet in question "use the hyperlinks", then I would open it and select White as the font colour for all cells (they won't be able to read anything with values the same colour as the fill) except one saying Please Use the hyperlinks as requested you **** then protect the sheet with a password. As far as I can tell, formulas linked to other sheets will still work. Without the password they won't be able to change the font colour even if they notice that the icon shows white.