By David Ringstrom, CPA


As you’re probably aware, it’s simple to hide a worksheet in Excel 2007: right-click on the worksheet tab, and then choose Hide. To unhide the worksheet, right-click on any worksheet tab, choose Unhide, and then select the sheet you wish to make visible. It’s a little more effort in earlier versions of Excel: choose Format, Sheet, and then Hide or Unhide to change the visible status of a worksheet.

Since most users know these tricks, hiding a worksheet is sometimes of little value, because someone else can simply unhide it. Protecting a workbook makes it harder, but savvy users then know that you have something to hide. In such cases, a user might visit www.lostpassword.com or search the Internet to secure an unlocking utility. However, you can take this arms race to new levels by making a worksheet “very hidden,” which will make it appear as if there are no hidden worksheets within your workbook. To do so, you must access the Visual Basic Editor:

 

• Excel 2007: Click the Visual Basic icon in the Code section of the Developer tab of the ribbon. If you don’t have a Developer tab, click the Office button, and then choose Excel options. In the Popular section, choose Show Developer Tab in Ribbon, and then click OK.
• Excel 2003 and earlier: Choose Tools, Macro, and then Visual Basic Editor.

Keyboard shortcut: Press Alt-F11 to access the Visual Basic Editor in any version of Excel.

Within the Visual Basic Editor you’ll need to make sure that both the Project Explorer and Properties Window are displayed, as shown in Figure 1. If you don’t see either of these, choose View, and then Project Explorer and/or Properties Window, as needed.

Figure 1: You may have to turn the Project Explorer and Properties windows on.

You’re now ready to hide an Excel worksheet in such a way that the vast majority of Excel users will never find it:

 

1. Locate the workbook in question within the Project Explorer window. Click once on the file name if you don’t see a Microsoft Excel Objects folder.
2. Within the Microsoft Excel Objects folder for a given workbook, you’ll see a list of worksheets, as shown in Figure 2. Click once on the worksheet that you wish to hide.

Figure 2: The Properties Window allows you to change the visible property of a worksheet.

 

1. Set the Visible property in the Properties Window to xlSheetVeryHidden, as shown in Figure 2. Note that by default the next worksheet on the list will be selected once you change this property, so it may appear that your change didn’t take effect. However, if you click on the worksheet name again in the Project Explorer window, you’ll see that the xlSheetVeryHidden property is set.
2. Choose File, and then Close and Return to Excel.

To test your handiwork, return to Excel, and try to unhide the worksheet. As shown in Figure 3, you should find that the Unhide command is disabled, even though the workbook contains a hidden worksheet. To make the sheet visible again, return to the Visual Basic Editor and set the Visible property for that worksheet back to xlSheetVisible.

Figure 3: The xlSheetVeryHidden will prevent the average user from discovering your hidden worksheets.

A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link