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.
- Boolean Properties
- The Unhide Dialogue
- Hiding Sheets More Thoroughly
The Usual Boolean Property
You may have noticed that many VBA object properties are Booleans. This means they can only be
.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
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.
[Sheet].Visible property surely fits this heuristic: is
.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
Sheet1.Visible = True
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
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,
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
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:
The Properties Window for
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
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
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.
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.
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
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.