Print Dialogue Box

[Macca_GB]

Hi, I came accross this really useful little routine to select specific sheets to print. The advantage for me of this routine is that each sheet is sent as its own print job abd not as a batch in a single job, therefore my page settings are maintained. The only thing I am struggling to so is add a select all check box, so that i can select all visible sheets and de-select the odd one or two. Can you have a look at the code and let me know if this can be acheived.

Many thanks in advance.

Code is:-

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

[romperstomper]

Rather than using the somewhat antiquated dialogsheets, I would suggest you use a userform with a multiselect listbox on it. That way all your controls can be added and coded at design time and all that happens at run-time is to populate the list with the sheet names.

[Macca_GB]

Thanks, no clue how to do that, so will research.

[romperstomper]

Here's a simple example. The listbox has had its MultiSelect property set.

[Macca_GB]

Rory, worked a treat and taught me loads abour user forms, thanks very much, appreciated.

[Macca_GB]

Hi, apologies for the duplication but I could not see my original post listed so thought it hadn't been submitted. As I now have a infraction point does that mean my query will wait?

Thanks