Hide A Sheet

You can hide specific sheets in a workbook using the Visible property of the Sheets object. You may want to hide sheets in a workbook to prevent others from veiwing them. Typically these sheets contain the raw values that you use to calculate data, and which displays on a separate sheet. Keep in mind, hiding a sheet does not keep a user from accessing it. Another user can unhide sheets in Excel using the Unhide option on the Format menu. If you have something that you do not want others to access, consider protecting as well as hiding the sheet. See the section "Protect a Worksheet" for more information about protecting sheets.

Using the Visible property, you can either determine the current state of a sheet — visible or not visible — or you can change the state of a sheet. To determine the current state of a sheet, you can assign the visible property to a Boolean variable as follows: SheetProps = Sheets(1).Visible.

If you declare the SheetProps variable as a Boolean value, the variable receives a value of True if the specified sheet is visible;otherwise, it receives a value of False. If you forget to declare the variable as Boolean, Excel assigns a numeric value of -1 if the sheet is visible, and 0 if the sheet is not visible.

You change the visibility of a sheet by assigning a Boolean value of True or False to the Visible property for the appropriate sheet. You can hide all but one sheet in a workbook. Excel requires that a workbook have at least one visible sheet. The following code illustrates how to hide a sheet so it is not visible: Sheet(2).Visible = False.

HIDE A SHEET

HIDE A SHEET

—n Create a new subroutine.

0 Type Dim LastSheet As Long, replacing LastSheet with the variable to contain the number of sheets in the workbook.

< Type LastSheet = Sheets.Count.

■ The For Next loop sets the Visible property for all but the first sheet.

—n Create a new subroutine.

0 Type Dim LastSheet As Long, replacing LastSheet with the variable to contain the number of sheets in the workbook.

< Type LastSheet = Sheets.Count.

■ The For Next loop sets the Visible property for all but the first sheet.

0 0

Post a comment