Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: VBA : Hide / Unhide Sheets

  1. #1
    Join Date
    5th April 2004
    Posts
    5
    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:

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680
    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

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Hide/Unhide Sheets Based On Cell Value
    By gmccreedy in forum EXCEL HELP
    Replies: 7
    Last Post: March 21st, 2015, 02:43
  2. Hide Unhide Row With CheckBox
    By Wallyboy in forum EXCEL HELP
    Replies: 7
    Last Post: September 15th, 2007, 02:43
  3. Hide/unhide Selected Range In All Sheets
    By Abi in forum EXCEL HELP
    Replies: 2
    Last Post: August 10th, 2007, 19:29
  4. hide and unhide a combo
    By adaniele in forum EXCEL HELP
    Replies: 2
    Last Post: April 24th, 2006, 09:45
  5. Hide/Unhide Sheets only working once...
    By Gra in forum EXCEL HELP
    Replies: 8
    Last Post: November 8th, 2005, 21:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno