Print Charts via dialog box

[Partjob]

I have this code which is kind of what I want. I am using it as a good explaination as to what I want to do as well as a starting point. I got it from "The Spreadsheet Page" It checks all the sheets in a workbook loads a dialog box and lists all the sheets with data in with a check box with an option to print them.

What I want is something very similar but checks for graphs. Obviously if there is a graph I would like it listed, so there is no need to check for data I think. I only have my graphs on a sheet named very originally "Graphs". So I don't think the code would need to check each sheet as this is doing.

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

[COLOR=#0000ff]'   Check for protected workbook[/COLOR]
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

[COLOR=#0000ff]'   Add a temporary dialog sheet[/COLOR]
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

[COLOR=#0000ff]'   Add the checkboxes[/COLOR]

    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
[COLOR=#0000ff]'       Skip empty sheets and hidden sheets[/COLOR]
        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

[COLOR=#0000ff]'   Move the OK and Cancel buttons[/COLOR]
    PrintDlg.Buttons.Left = 240

[COLOR=#0000ff]'   Set dialog height, width, and caption[/COLOR]
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"

    End With

[COLOR=#0000ff]'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus[/COLOR]
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

[COLOR=#0000ff]'   Display the dialog box[/COLOR]
    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
[COLOR=#0000ff]'                   ActiveSheet.PrintPreview 'for debugging[/COLOR]

                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If
</pre>

I have tried to adjust the various lines to relate to charts instead of sheets but was getting in a right kerfuffle. Whats more I have only just mastered the art of refering to sheets nevermind charts. I never dealt with dialog boxes.


This does seem a pretty big ask I know you like us to at least have try, but I know I will spead days on this without any help.

Thanks a lot, this will save my sanity if nothing else.
Partjob

 

[Norie]

Throughout the code Worksheets are being referred to, so chart sheets will be ignored.

If you wanted to list all the charts on a worksheet you would need something like this.

Dim cht
    For Each cht In Worksheets("Graphs").ChartObjects
        MsgBox cht.Name
    Next cht

Which might fit in to the original code like this, which is untested.

Sub SelectSheets()
    Dim cht As Object
    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 Each cht In Worksheets("Graphs").ChartObjects
'       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 = _
                    cht.Name
            TopPos = TopPos + 13
        End If
    Next cht
'   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
End Sub

 

[Partjob]

Thanks Norie that has done the trick

Partjob