A Microsoft Excel 2003 or Excel 2007 workbook may contain multiple Spreadsheets (commonly known as Sheets or Tabs). Occasionally it is desired that the sheets are hidden from view. An example is where charts need to be displayed however the underlying data is not required to be seen. There are two options available for assigning sheet visibility properties in an Excel workbook as well as three properties being available (whether that’s Excel 2003 or Excel 2007):
Excel Sheet Visibility Properties
Visibility describes whether, when an Excel workbook is open, the sheets can be seen by the person viewing the file. Three visibility properties are available:
- xlSheetVisible makes the sheet visible;
- xlSheetHidden hides the sheet, but it can be made visible by any user through the Excel user interface or VBA code;
- xlSheetVeryHidden hides the sheet and the user cannot make the sheet visible through the Excel user interface (a sheet can only be made ‘very hidden’ or visible from a ‘very hidden’ state through VBA code). Accessibility to the VBA code can be restricted so this property is ideal when data is required to be hidden from, for example, a client.
Assigning Visibility Properties
There are two methods available for assigning visibility properties to sheets;
Excel User Interface: Hiding or unhiding a sheet may be achieved through clicking the following sequence within Excel;
- Excel 2003: Format -> Sheet -> Hide or Unhide
- Excel 2008: Home -> Format (in Cells Group) -> Hide & Unhide -> Unhide Sheet or Hide Sheet.
When clicking ‘Hide’ in Excel 2003 or ‘Hide Sheet’ in Excel 2007, the xlSheetHidden property is assigned to the sheet to be hidden. The xlSheetVeryHidden property cannot be assigned using the Excel user interface. The video below describes using the Excel 2003 user interface to hide a sheet.
Microsoft Visual Basic for Applications (VBA): To assign the xlSheetVeryHidden property (or assign the xlSheetVisible property when the sheet has the xlSheetVeryHidden property assigned), VBA code must be used. This is very simple to achieve as may be seen from the video below (the video shows Excel 2003 however the actions are the same for excel 2007):
As can be seen, hiding sheets is very simple. The most relevant decision is whether to use the xlSheetHidden or xlSheetVeryHidden property.