Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,677
    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
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    OK, here&#039;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
    Kind Regards, Will Riley

    Web Presence:
    Personal: Datasapien
    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: 6
    Last Post: August 7th, 2014, 06:39
  2. Hide Unhide Row With CheckBox
    By Wallyboy in forum EXCEL HELP
    Replies: 7
    Last Post: September 15th, 2007, 01:43
  3. Hide/unhide Selected Range In All Sheets
    By Abi in forum EXCEL HELP
    Replies: 2
    Last Post: August 10th, 2007, 18:29
  4. hide and unhide a combo
    By adaniele in forum EXCEL HELP
    Replies: 2
    Last Post: April 24th, 2006, 08:45
  5. Hide/Unhide Sheets only working once...
    By Gra in forum EXCEL HELP
    Replies: 8
    Last Post: November 8th, 2005, 20: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