VBA/Excel/Access/Word/Excel/Worksheet Hide

Материал из VB Эксперт
Перейти к: навигация, поиск

Hides the worksheet

   <source lang="vb">

Sub HideWorksheet(ws As Worksheet, bVeryHidden As Boolean)

   If bVeryHidden Then
       ws.Visible = xlSheetVeryHidden
   Else
       ws.Visible = xlSheetHidden
   End If

End Sub

</source>
   
  


Hiding and Unhiding Worksheets

   <source lang="vb">

Sub HideWorksheet(sName As String, bVeryHidden As Boolean)

   If WorksheetExists(ThisWorkbook, sName) Then 
       If bVeryHidden Then 
           ThisWorkbook.Worksheets(sName).Visible = xlSheetVeryHidden 
       Else 
           ThisWorkbook.Worksheets(sName).Visible = xlSheetHidden 
       End If 
   End If 

End Sub Function WorksheetExists(wb As Workbook, sName As String) As Boolean

   Dim s As String 
   On Error GoTo bWorksheetExistsErr 
   s = wb.Worksheets(sName).Name 
   WorksheetExists = True 
   Exit Function 

bWorksheetExistsErr:

   WorksheetExists = False 

End Function

</source>
   
  


To hide specific worksheet

   <source lang="vb">

Sub Hide_WS1()

   Worksheets(2).Visible = Hide  " you can use Hide or False

End Sub

</source>
   
  


To make a specific worksheet very hidden

   <source lang="vb">

Sub Hide_WS2()

   Worksheets(2).Visible = xlVeryHidden

End Sub

</source>
   
  


To set the visible property to True on ALL sheets in workbook

   <source lang="vb">

Sub Un_Hide_All()

   Dim sh As Worksheet
   For Each sh In Worksheets
       sh.Visible = True
   Next

End Sub

</source>
   
  


To set the visible property to xlVeryHidden on ALL sheets in workbook.

   <source lang="vb">

Sub xlVeryHidden_All_Sheets()

   On Error Resume Next
   Dim sh As Worksheet
   For Each sh In Worksheets
       sh.Visible = xlVeryHidden
   Next

End Sub

</source>
   
  


To toggle between hidden and visible

   <source lang="vb">

Sub Toggle_Hidden_Visible()

   Worksheets(2).Visible = Not Worksheets(2).Visible

End Sub

</source>
   
  


To unhide a specific worksheet

   <source lang="vb">

Sub UnHide_WS()

   Worksheets(2).Visible = True

End Sub

</source>
   
  


Unhiding Every Worksheet in a Workbook.

   <source lang="vb">

Sub UnhideAllWorksheets()

   Dim ws As Worksheet 
   For Each ws In ThisWorkbook.Worksheets 
       ws.Visible = xlSheetVisible 
   Next ws 
   Set ws = Nothing 

End Sub

</source>