VBA : Hide / Unhide Sheets

**TimBray** · April 16th, 2004

Help again,

I have found on the web a fantasitc piece of code (below) that prints from a form. This works like a charm!

I would like to use this same type of form to select both hide, unhide and very hidden sheet options, the code would be executed by a control button on my cover page of the workbook the same way I use this code for 'selecting sheets to print'.

I have tried playing around with this code to modify it to what I need but have crashed and burned...Any help would be appreciated.
Thanks
Tim

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate
End Sub:biggrin:

***Will Riley*** · April 16th, 2004

This seems to work

VB:

Option Explicit 
 
Sub SelectSheets() 
    Dim i As Integer 
    Dim TopPos As Integer 
    Dim SheetCount As Integer 
    Dim PrintDlg As DialogSheet 
    Dim CurrentSheet As Worksheet 
    Dim cb As CheckBox 
    Application.ScreenUpdating = False 
     
    ' Check For protected workbook 
    If ActiveWorkbook.ProtectStructure Then 
        MsgBox "Workbook is protected.", vbCritical 
        Exit Sub 
    End If 
     
    ' Add a temporary dialog sheet 
    Set CurrentSheet = ActiveSheet 
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add 
     
    SheetCount = 0 
     
    ' Add the checkboxes 
    TopPos = 40 
    For i = 1 To ActiveWorkbook.Worksheets.Count 
        Set CurrentSheet = ActiveWorkbook.Worksheets(i) 
        ' Skip empty sheets And hidden sheets 
        If Application.CountA(CurrentSheet.Cells) <> 0 Then 
            SheetCount = SheetCount + 1 
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 
            PrintDlg.CheckBoxes(SheetCount).Text = _ 
            CurrentSheet.Name 
            TopPos = TopPos + 13 
        End If 
    Next i 
     
    ' Move the OK And Cancel buttons 
    PrintDlg.Buttons.Left = 240 
     
    ' Set dialog height, width, And caption 
    With PrintDlg.DialogFrame 
        .Height = Application.Max _ 
        (68, PrintDlg.DialogFrame.Top + TopPos - 34) 
        .Width = 230 
        .Caption = "Select sheets to print" 
    End With 
     
    ' Change tab order of OK And Cancel buttons 
    ' so the 1st option button will have the focus 
    PrintDlg.Buttons("Button 2").BringToFront 
    PrintDlg.Buttons("Button 3").BringToFront 
     
    ' Display the dialog box 
    CurrentSheet.Activate 
    Application.ScreenUpdating = True 
    If SheetCount <> 0 Then 
        If PrintDlg.Show Then 
            For Each cb In PrintDlg.CheckBoxes 
                If cb.Value = xlOn Then 
                    If Not Worksheets(cb.Caption).Visible Then 
                        With Worksheets(cb.Caption) 
                            .Visible = True 
                            .Activate 
                            .PrintOut 
                            .Visible = False 
                        End With 
                    Else 
                        With Worksheets(cb.Caption) 
                            .Activate 
                            .PrintOut 
                        End With 
                    End If 
                    ' ActiveSheet.PrintPreview 'for debugging 
                End If 
            Next cb 
        End If 
    Else 
        MsgBox "All worksheets are empty." 
    End If 
     
    ' Delete temporary dialog sheet (without a warning) 
    Application.DisplayAlerts = False 
    PrintDlg.Delete 
     
    ' Reactivate original sheet 
    CurrentSheet.Activate 
End Sub

At the moment, the code merely "Hides" sheets that are not visible... i.e. it does not set veryhidden sheets back to veryhidden... just hides them... would this be OK... otherwise a function could be added to test for sheet status - i.e. 1 is visible, 2 hidden, 3 very hidden... then use a Select Case statement instead of all those IFs to perform your unhiding, rehiding to put things back as they were...

Hope it helps

Will

***Will Riley*** · April 16th, 2004

OK, here's the code to put the visible status back how it was

VB:

Option Explicit 
 
Sub SelectSheets() 
    Dim i As Integer 
    Dim TopPos As Integer 
    Dim SheetCount As Integer 
    Dim PrintDlg As DialogSheet 
    Dim CurrentSheet As Worksheet 
    Dim cb As CheckBox 
    Application.ScreenUpdating = False 
     
    ' Check For protected workbook 
    If ActiveWorkbook.ProtectStructure Then 
        MsgBox "Workbook is protected.", vbCritical 
        Exit Sub 
    End If 
     
    ' Add a temporary dialog sheet 
    Set CurrentSheet = ActiveSheet 
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add 
     
    SheetCount = 0 
     
    ' Add the checkboxes 
    TopPos = 40 
    For i = 1 To ActiveWorkbook.Worksheets.Count 
        Set CurrentSheet = ActiveWorkbook.Worksheets(i) 
        ' Skip empty sheets And hidden sheets 
        If Application.CountA(CurrentSheet.Cells) <> 0 Then 
            SheetCount = SheetCount + 1 
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 
            PrintDlg.CheckBoxes(SheetCount).Text = _ 
            CurrentSheet.Name 
            TopPos = TopPos + 13 
        End If 
    Next i 
     
    ' Move the OK And Cancel buttons 
    PrintDlg.Buttons.Left = 240 
     
    ' Set dialog height, width, And caption 
    With PrintDlg.DialogFrame 
        .Height = Application.Max _ 
        (68, PrintDlg.DialogFrame.Top + TopPos - 34) 
        .Width = 230 
        .Caption = "Select sheets to print" 
    End With 
     
    ' Change tab order of OK And Cancel buttons 
    ' so the 1st option button will have the focus 
    PrintDlg.Buttons("Button 2").BringToFront 
    PrintDlg.Buttons("Button 3").BringToFront 
     
    ' Display the dialog box 
    CurrentSheet.Activate 
    Application.ScreenUpdating = True 
    If SheetCount <> 0 Then 
        If PrintDlg.Show Then 
            For Each cb In PrintDlg.CheckBoxes 
                If cb.Value = xlOn Then 
                    Select Case Worksheets(cb.Caption).Visible 
                    Case Is = -1 'visible 
                        With Worksheets(cb.Caption) 
                            .Activate 
                            .PrintOut 
                        End With 
                    Case Is = 0 'hidden 
                        With Worksheets(cb.Caption) 
                            .Visible = True 
                            .Activate 
                            .PrintOut 
                            .Visible = False 
                        End With 
                    Case Is = 2 'veryhidden 
                        With Worksheets(cb.Caption) 
                            .Visible = True 
                            .Activate 
                            .PrintOut 
                            .Visible = 2 
                        End With 
                    End Select 
                    ' ActiveSheet.PrintPreview 'for debugging 
                End If 
            Next cb 
        End If 
    Else 
        MsgBox "All worksheets are empty." 
    End If 
     
    ' Delete temporary dialog sheet (without a warning) 
    Application.DisplayAlerts = False 
    PrintDlg.Delete 
     
    ' Reactivate original sheet 
    CurrentSheet.Activate 
End Sub

Will