SubscribeHow to Hide Worksheet Tabs in Excel Workbook
Reem, one of the PHD readers, asks in e-mail,
Is there a way to prevent users from unhiding “hidden sheets” in an excel file – without using VBA?
or to put it in other words, can the “Format/Sheet/Unhide” be disabled for specific worksheets?
Here is a non-VBA way to do this. I am not sure if this is optimum, but it seems to produce results without much effort. And it doesn’t use VBA, just the VBA Editor.
Step 1: Right click on the tab you want to hide and select view code option
Step 2: In the properties window for that sheet, set “visibility” as 2 – xlSheetVeryHidden
Step 3: Now right click on the sheet name in project explorer area and select VBA Project properties
Step 4: Go to “Protection” tab and check “Lock” project
Step 5: and set password for protection, click ok
Step 6: when someone tries to open the VBA Code for that sheet to make the worksheet tab unhidden (visible), Excel prompts for a password
This trick is very handy when you are sharing workbooks with others and afraid that they may ruin the calculations or data.
|
Trackbacks & Pingbacks
- Pingback by 100 Excel Tips & Resources for Everyone | Pointy Haired Dilbert - Chandoo.org on January 28, 2009 @ 12:37 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
It’s a —- ! Not to hide a sheet but to protect VBA……. This protection is very easy to destroy and it’s not a real protection… Try Acyd.xla to convince you… you will see !
To download Acyd.xla, google it !
@Souri: Welcome
I think you missed the step on changing the visibility to xlSheetVeryHide. Even if you do that and dont add VBA protection, you will be able to revert back the sheet by changing the visibility property. That is why you need to add VBA protection.
Of course, you can always break any type of protection using some crackware, that is not the point of this discussion though.
And, we would appreciate if you use a more acceptable language to share your enthusiasm.
Speaking of things you can (or can’t) do with an Excel worksheet tab; the other day I unconsciously reached for an affordance that wasn’t there. I was given a workbook with a confusing number of tabs on, so I thought “I know! colour coding them will make it easier to navigate” and went to change the colours of the tabs.
Of course Excel has no such capability, as I would have remembered if I wasn’t working in the morning before coffee. After all, Quattro Pro only had it twenty years ago.
Derek,
I might be misunderstanding your question, but as far as I know Excel lets you change the color of the tabs already since Version Excel 2002 (right click on the tab and choose tab color).
In Excel 2007, you can also disable that menu choice using RibbonX xml code. It isn’t secure, but the average user won’t have a clue how to unhide it.
Chandoo…
I have been visiting your blog for the past couple of months and I must say, it is superb. Each post is useful and I often use what you share at my work. Via your site, I have found quite a few other incredible excel blogs.
Next time you are in Bangalore, the coffee is on me.
Cheers,
D.
Oh, that’s good to know. Unfortunately I’ve only got 2000 at work. We were on the very brink of getting 2003 (not 2007!) when the recession hit and all our IT spending was cancelled.
Thank you Mr.Chandoo for giving many useful tips.I am so attached to chandoo.org,coz its beefing up my excel knowledge tank day by day.thanks a lot.
@Suryachandra: You are welcome
@Sumit, sweet.. free coffee. I might come to Bangalore in the next month or so for some work.
@Tim: that is very good to know. Thanks for sharing it with us. But if we disable the menu choice using RibbonX code and send the workbook to someone else, wont they be still having access to the menu choice.. ?
@derek: that means you have a stable version of excel at work for the next few years. no more wasting time with creating compatible versions and coping with new features..
We were going to get a stable version. I was really impressed when I heard the upgrade was going to be to XP and Office 2003; I said “You can still get those!?”
That’s why I’m frustrated by the cancellation: we were so close to something that would stand good for many more years, and now who knows if we’ll be stuck with Vista and 2007 when the spending finally comes through? While everyone else moves on to whatever MS manages to salvage from the mess.
Hi Chandoo,
I have an excel which is suppposed to have 3 sheets, but at one time only one sheet is visible.
Also, I cannot see any tabs.How the heck do I unhide all the sheets, make them visible at the same time, and how the hell do I get my tabs back.
-Sindhu
@Sindhu: The excel workbook must be having “hide sheet tabs” option turned on. You can turn it off from excel options > Advanced options in excel 2007 or excel options menu item in excel 2003.
Also, it might be possible that the excel workbook has some macros which might be causing this behavior. In that case, turn off the macros and open the workbook again and you might be able to see all tabs.
I tried to set a pass for the code, but is not working. I´v tried on all of the sheets (7) on my workbook, I tried on the VBA project, but when i close and reopen, the pass is gone. And yes, i have save changes before closing.
Can anyone help me? Thanks.
@Liliana… which version of excel you are using? The tip has been tested in excel 2003.
Hi Chandoo,
The hide under format is grayed out and I’m unable to hide the sheet using the VBA.
it throws up an error like this Unable to set the Visible property of the worksheet class.
Please help