Announcement

Collapse
No announcement yet.

VBA : Hide / Unhide Sheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA : Hide / Unhide Sheets



    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

    &#039; Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

    &#039; 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

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

    &#039; 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
    &#039; ActiveSheet.PrintPreview &#039;for debugging
    End If
    Next cb
    End If
    Else
    MsgBox "All worksheets are empty."
    End If

    &#039; Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

    &#039; Reactivate original sheet
    CurrentSheet.Activate
    End Sub:biggrin:

  • #2
    This seems to work

    Code:
    Option Explicit
    
    Sub SelectSheets&#40;&#41;
    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
    
    &#039; Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
    MsgBox "Workbook is protected.", vbCritical
    Exit Sub
    End If
    
    &#039; Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add
    
    SheetCount = 0
    
    &#039; Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
    Set CurrentSheet = ActiveWorkbook.Worksheets&#40;i&#41;
    &#039; Skip empty sheets and hidden sheets
    If Application.CountA&#40;CurrentSheet.Cells&#41; <> 0 Then
    SheetCount = SheetCount + 1
    PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
    PrintDlg.CheckBoxes&#40;SheetCount&#41;.Text = _
    CurrentSheet.Name
    TopPos = TopPos + 13
    End If
    Next i
    
    &#039; Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240
    
    &#039; Set dialog height, width, and caption
    With PrintDlg.DialogFrame
    .Height = Application.Max _
    &#40;68, PrintDlg.DialogFrame.Top + TopPos - 34&#41;
    .Width = 230
    .Caption = "Select sheets to print"
    End With
    
    &#039; Change tab order of OK and Cancel buttons
    &#039; so the 1st option button will have the focus
    PrintDlg.Buttons&#40;"Button 2"&#41;.BringToFront
    PrintDlg.Buttons&#40;"Button 3"&#41;.BringToFront
    
    &#039; 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&#40;cb.Caption&#41;.Visible Then
                    With Worksheets&#40;cb.Caption&#41;
                    .Visible = True
                    .Activate
                    .PrintOut
                    .Visible = False
                    End With
                Else
                With Worksheets&#40;cb.Caption&#41;
                .Activate
                .PrintOut
                End With
                End If
    &#039; ActiveSheet.PrintPreview &#039;for debugging
            End If
            Next cb
        End If
    Else
    MsgBox "All worksheets are empty."
    End If
    
    &#039; Delete temporary dialog sheet &#40;without a warning&#41;
    Application.DisplayAlerts = False
    PrintDlg.Delete
    
    &#039; 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
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3


      OK, here&#039;s the code to put the visible status back how it was

      Code:
      Option Explicit
      
      Sub SelectSheets&#40;&#41;
      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
      
      &#039; Check for protected workbook
      If ActiveWorkbook.ProtectStructure Then
      MsgBox "Workbook is protected.", vbCritical
      Exit Sub
      End If
      
      &#039; Add a temporary dialog sheet
      Set CurrentSheet = ActiveSheet
      Set PrintDlg = ActiveWorkbook.DialogSheets.Add
      
      SheetCount = 0
      
      &#039; Add the checkboxes
      TopPos = 40
      For i = 1 To ActiveWorkbook.Worksheets.Count
      Set CurrentSheet = ActiveWorkbook.Worksheets&#40;i&#41;
      &#039; Skip empty sheets and hidden sheets
      If Application.CountA&#40;CurrentSheet.Cells&#41; <> 0 Then
      SheetCount = SheetCount + 1
      PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
      PrintDlg.CheckBoxes&#40;SheetCount&#41;.Text = _
      CurrentSheet.Name
      TopPos = TopPos + 13
      End If
      Next i
      
      &#039; Move the OK and Cancel buttons
      PrintDlg.Buttons.Left = 240
      
      &#039; Set dialog height, width, and caption
      With PrintDlg.DialogFrame
      .Height = Application.Max _
      &#40;68, PrintDlg.DialogFrame.Top + TopPos - 34&#41;
      .Width = 230
      .Caption = "Select sheets to print"
      End With
      
      &#039; Change tab order of OK and Cancel buttons
      &#039; so the 1st option button will have the focus
      PrintDlg.Buttons&#40;"Button 2"&#41;.BringToFront
      PrintDlg.Buttons&#40;"Button 3"&#41;.BringToFront
      
      &#039; 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&#40;cb.Caption&#41;.Visible
                  Case Is = -1 &#039;visible
                      With Worksheets&#40;cb.Caption&#41;
                      .Activate
                      .PrintOut
                      End With
                  Case Is = 0 &#039;hidden
                      With Worksheets&#40;cb.Caption&#41;
                          .Visible = True
                          .Activate
                          .PrintOut
                          .Visible = False
                      End With
                  Case Is = 2 &#039;veryhidden
                      With Worksheets&#40;cb.Caption&#41;
                          .Visible = True
                          .Activate
                          .PrintOut
                          .Visible = 2
                      End With
                  End Select
      &#039; ActiveSheet.PrintPreview &#039;for debugging
              End If
              Next cb
          End If
      Else
      MsgBox "All worksheets are empty."
      End If
      
      &#039; Delete temporary dialog sheet &#40;without a warning&#41;
      Application.DisplayAlerts = False
      PrintDlg.Delete
      
      &#039; Reactivate original sheet
      CurrentSheet.Activate
      End Sub
      Will
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment

      Working...
      X