Listing Hiding and Unhiding Worksheets

'/ Hides the worksheet named sName

Sub HideWorksheet(sName As String, bVeryHidden As Boolean) If WorksheetExists(ThisWorkbook, sName) Then If bVeryHidden Then

ThisWorkbook.Worksheets(sName).Visib1e = xlSheetVeryHidden

Else

ThisWorkbook.Worksheets(sName).Visib1e = xlSheetHidden End If End If End Sub

Sub UnhideWorksheet(sName As String)

If WorksheetExists(ThisWorkbook, sName) Then

ThisWorkbook.Worksheets(sName).Visible = xlSheetVisible End If

End Sub

Sub UsingHideUnhide()

Dim lResponse As Long

' hide the worksheet HideWorksheet "Sheet2", True

' show that it is hidden - ask to unhide lResponse = MsgBox("The worksheet is very hidden. Unhide?", vbYesNo)

If lResponse = vbYes Then

UnhideWorksheet "Sheet2" End If

End Sub

Listing 7.4 uses the WorksheetExists procedure from Listing 7.2 to make sure that the worksheet name passed using the sName parameter actually exists. Once you verify that the worksheet exists, you can get to the required Worksheet object by referring to a specific item from the Worksheets object associated with the ThisWorkbook object. Finally, you can set the Visible property accordingly. The constant xlSheetVeryHidden hides the worksheet so that it can't be unhidden using Format ^ Sheet ^ Unhide. The constant xlSheetHidden hides the sheet normally as if you choose Format ^ Sheet ^ Hide from the Excel menu.

Listing 7.4 also lists a procedure called UnhideWorksheet. You may occasionally need this, but if you already have a worksheet variable, you might just unhide the worksheet directly.

An example procedure appears in Listing 7.4 that shows the use of HideWorksheet and Unhide-Worksheet.

Once in awhile, you'll have multiple hidden worksheets and you'll want to ensure that all of them are visible. You can easily unhide them all by looping through each worksheet in the workbook, as shown in Listing 7.5.

0 0

Post a comment