Results 1 to 25 of 25

Display a menu of worksheets to print out

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-13-2009
    Location
    Lincoln, IL
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    237

    Question Display a menu of worksheets to print out

    I came across the following macro that generates a list of worksheets with check boxes to select the ones I want to print. I attached the macro to a button on my "Totals" worksheet. When I run the macro, it lets me pick the worksheets, but doesn't print anything unless "Totals" is one of the worksheets I selected in the dialog box. What is missing?

    I also want to stop the screen flickering and have it go back to the "Totals" page when it's done since that's where the button is.

    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 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
    Last edited by narrowgate88; 06-24-2009 at 04:00 PM.

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1