Displaying A Menu Of Worksheets To Print

Category: Printing / UserForms | [Item URL]

This tip contains a VBA subroutine that displays a dialog box that contains the names of all non-empty worksheets in the active workbook. The names are displayed as checkboxes. The user can select which sheets to print, and click OK. The macro then prints the selected worksheets.

This subroutine is rather unusual since it creates the dialog box on the fly, and then deletes it after it is dismissed.

The code below prints the selected worksheets, but this general technique can be adapted to other situations in which you need the user to specify one or more sheets in a workbook.

Although this procedure uses an Excel 5/95 Dialog Sheet, it will also work with later versions of Excel. By the way, this is a rare example of how using a Dialog Sheet is easier than using a UserForm. Creating a UserForm on the fly is much more difficult, but it is possible.

Using the code

To try out the example, copy the code below and paste it to a VBA module (there is no need to add a dialog sheet). Then execute the SelectSheets subroutine.

The SelectSheets subroutine

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

NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.

    If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Select Replace:=False 
                End If
            Next cb
            ActiveWindow.SelectedSheets.PrintOut copies:=1 
            ActiveSheet.Select 
    End If