In keeping with our recent theme of making workbooks easier to navigate (hiding columns, deleting columns), this post will take a look at the .Visible property of sheets and its three possible values. This property lets you hide and unhide sheets with its three options: True, xlSheetHidden, and xlSheetVeryHidden.

Knowing how to hide sheets with VBA is valuable if you need to clean up the user interface or protect calculations to prevent accidental modification.

The Usual Boolean Property

You may have noticed that many VBA object properties are Booleans. This means they can only be True or False. Common Boolean properties include .Hidden for columns/rows, font properties like Font.Bold, and text properties like Cells.WrapText. These can only take Boolean values, and that intuitively makes sense. How can the Bold property of a font be any value other than True or False?

To determine whether a property is likely Boolean, you can ask the simple question “Is the object property?” For example, “is the font bold?” or “is the column hidden?”. If the answer is naturally yes/no, then it’s most likely a Boolean property.

The [Sheet].Visible property surely fits this heuristic: is Sheet1 visible? It is natural to answer this question with a yes or no. To complicate it even more, the values True and False can certainly be assigned to the sheet object’s .Visible property. You can hide a sheet using the [Immediate window] (press Ctrl+g in the VBE to open it) by executing this code:

Sheet1.Visible = False

and Sheet1 will be hidden from view in the tabs at the bottom of the Excel window. To show it again, set the property to True:

Sheet1.Visible = True

and Sheet1 will reappear.

If you have Intellisense enabled - which I recommend for everyone, but especially those learning VBA - you might have noticed that [Sheet].Visible does not display True and False as its options, though.

The Unhide Dialogue

One reason to hide sheets is to clean up the interface so it’s easier for users to navigate. This applies to complex workbooks wherein a single sheet handles inputs and a single sheet for results suffices to satisfy user needs, while perhaps 5 sheets store useful historical data and 3 set up and perform calculations. The 8 data and processing sheets are not really necessary for the user, and it can clutter up the display or even confuse inexperienced users.

By right-clicking on the sheets group at the bottom of the Excel window, users can look at hidden sheets. That is, they can see a list of sheets whose .Visible property is set to False. They can then unhide those sheets to look at or change them.

Unhide Dialogue
The Unhide Dialogue, which lists hidden sheets and allows the user to re-display them

What if you want to make sheets more hidden? For example, if you want to prevent users from accidentally - or maliciously - changing formulas. To do this, we can use the third legal value for the .Visible property of sheets, xlSheetVeryHidden.

Hiding Sheets More Thoroughly

To avoid issues with curious users who may want to see what sheets are hidden, you can use VBA to obscure especially sensitive sheets from a GUI-user’s view. Instead of setting the .Visible property to False (which is the same as setting it to xlSheetHidden), you can set it to a third value: xlSheetVeryHidden. This makes the sheet invisible not only on the tab tray but also in the Unhide Dialogue, so users can’t even unhide it that way.

Hide the sheet graphically in VBE with the Properties window (usually shortcutted via F4 while the VBE is open). Change the Visible property manually via the red box here:

Properties Window for Sheet1
The Properties Window for Sheet1 with Visible property highlighted

Or, of course, you can set the .Visible property in code:

Sub HideSheetExample()
Sheet1.Visible = xlSheetVeryHidden
End Sub

This example uses the VBA CodeName Sheet1 to hide the sheet, but you could also spell out the sheet name, like Sheets("Sheet1").

The only way for a Very Hidden sheet to be unhidden is through the VBA editor. For less-skilled users, they might not even know how to enable the Developer tab, while others may not know how to use VBA. This certainly is not a secure solution against an adept attacker, but it will likely keep most coworkers and clients from unhiding and inadvertently breaking sheets.

There are shorter ways to make a sheet very hidden, though. The value xlSheetVeryHidden is the same as xlVeryHidden, just like a value False is the same as a value of xlSheetHidden, which is the same as a value of xlHidden. You can use whichever nomenclature you like!

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

Protecting from Prying Eyes

If you want, you can set passwords on VBA Projects through the Tools > VBAProject Properties... menu item (blue box below). On the Protection tab, add your password and check the Lock project for viewing checkbox (red box). When you close the Excel file and re-open it, the project will be collapsed in the Project Explorer window and no one will be able to view the object properties or code modules without entering the password.

Password Protection Window with VBAProject Properties
Password Protection Window with VBAProject Properties

A Note on Security

If you’re worried about users maliciously changing the sheets or stealing intellectual property, using a password to protect the project is not very secure. If you have highly sensitive data on hidden sheets, I’d recommend another approach. If you are simply comparing inputs, at least store hashed data instead of plaintext. If you need to operate on the data, consider a database connection that Excel can query every time it needs to operate on the data.

For most office applications, a very high level of security is not necessary, and password protecting your VBA project will suffice. If you need more security on your spreadsheets, I typically recommend Unviewable+. It’s not invincible, but it does a good job protecting against the most common hacking techniques.

Hopefully this tutorial taught you how to hide, unhide, and make sheets very hidden using VBA. It may have surprised you that .Visible is not a Boolean property, since most properties are. With a Very Hidden sheet, you can stop some curious users from seeing sensitive information or accidentally changing formulas on sheets for calculations.

If you want, you can even password protect your VBA projects now, but bear in mind that a weak password may be easily broken. And if you have to protect data from truly malicious actors, I would recommend another approach. The average curious office user, though, will likely be thwarted by password protection and Very Hidden sheets.

To learn more VBA tips like this one, subscribe using the form below.