The focus of this blog is on providing tips, tricks, and information that beginning developers will find useful. Examples of topics I will cover include how to debug and troubleshoot macros, useful features of the Visual Basic Editor (VBE), and cleaning up recorded macros. Along the way, I hope to present the topics using examples and code snippets that you can immediately put to good use.
Also, in keeping with the times, all of my instructions assume Office 2007, but whenever possible I will also provide instructions for Office 2003. The user interface is quite different between the two versions, which complicates the ability to give generic step-by-step instructions that apply to all versions. Therefore, expect to see two sets of instructions to avoid complicating matters.
As the “old” menus are familiar to most people, I’m going to assume that you already know where the menu items are in Office 2003 instructions. For Office 2007, I’ll refer to items on the ribbon using a Tab/Chunk/Item/Sub-Item convention, which is similar to how Office Letter instructions are written for any menu-based application. For Excel, I’ll write that the format cells command is found by navigating to Home/Cells/Format/Format Cells.
The VBE does not use a ribbon, so any references to menu items in the VBE are the same across Office 2003 and 2007. Whew!
VERY HIDDEN WORKSHEETS
The topic of this week’s article is very hidden Excel worksheets. Very hidden worksheets are hidden Excel worksheets that cannot be unhidden using the Excel user interface. As you might expect, they do not appear in the Unhide dialog box. From an end-user perspective, it’s as if the worksheets didn’t exist.
There are only two ways to hide/unhide very hidden worksheets: programmatically or using the VBE. Consequently, not only is this a cool tip, but it’s also a great way to introduce two windows that you’ll use frequently when editing VBA macros and building user forms; the Project Explorer window and the Properties window.
The only way to toggle the visibility of a very hidden worksheet without writing a line of code is using the Project Explorer window in conjunction with the Properties window in the VBE.
Here’s how it’s done in both Excel 2003 and 2007.
1. Open a new workbook in Excel.
2. Press Alt+F11 to open the Visual Basic Editor.
3. If Project Explorer and Properties windows aren’t already visible (or if you don’t know what they look like), choose View/Project Explorer (shortcut: Ctrl + R) followed by View/Properties Window (shortcut: F4). In the figure below (from Excel 2003), the Project Explorer is in the upper-left corner of the window and the Properties window is below it.
5. In the Properties window, find the Visible property and set it to:
2 — xlSheetVeryHidden
Note an annoying behavior that occurs when you do this: the VBE moves off of Sheet2, giving the appearance in the Properties window that you didn’t set the property correctly.
Now switch back to the Excel window and observe its behavior. First, you’ll see that Sheet2 is hidden. Note that the Unhide dialog item in Excel 2007 is disabled (Home/Cells/Format/Hide and Unhide/Unhide Sheet is “grayed out” in Excel 2007) and in Excel 2003 Sheet 2 does not appear in the list of hidden sheets when you use the Window/Unhide menu command.
Now try this:
1. Right-click on Sheet3 and select Hide in Excel 2007; in Excel 2003, switch to Sheet 3 and use the Window/Hide command.
2. Display the Unhide dialog (in Excel 2007 use Home/Cells/Format/Hide and Unhide/Unhide Sheet, in Excel 2003 use Window/Unhide) and you will see that Sheet3 is the only item listed.
To view the Sheet2 again, switch back over to the VBE (Alt + F11) and set the Visible property of Sheet2 back to
-1 — xlSheetVisible.
Yes, that’s a negative “1” at the beginning of the option.
Remember that while very hidden worksheets are very stealthy, you should not rely on visibility alone to protect sensitive data or critical operations. Knowledgeable users (such as yourself, after reading this tip) can locate very hidden worksheets and figure out ways to retrieve the data contained on them.