Loop through one or multiple selected Chart Objects-CodePudding

I want to write a code that works whether if I select one or multiple charts in Excel.

Actually I use this code, but it fail when only one chart is seleted :

Dim obj As Object

'Check if any charts have been selected
If Not ActiveChart Is Nothing Then

    MsgBox ("Select multiple charts")

Else
    For Each obj In Selection

    'If more than one chart selected
    If TypeName(obj) = "ChartObject" Then

        Call AnotherMacro(obj.Chart)

    End If
Next

End If

I'm looking for a solution working into a unique Sub.

Thanks for your help

CodePudding user response：

Please, try the next way:

Sub iterateSelectedCharts()
    Dim obj As Object
    If TypeName(Selection) = "ChartArea" Then 'for the case of only one selected chart:
        'Call AnotherMacro(ActiveChart)
    ElseIf TypeName(Selection) = "DrawingObjects" Then
        For Each obj In Selection
            If TypeName(obj) = "ChartObject" Then
                'Call AnotherMacro(obj.Chart)
            End If
        Next obj
    Else
        MsgBox ("Please, select charts") 'for the case when selection contains
                                         'other type of object(s): cells, rectangles, circles etc.
    End If
End Sub

CodePudding user response：

ActiveChart is set, if only one chart is selected. If you select none or more, ActiveChart is Empty. You need to loop over each chart like this:

Dim obj As Object
For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
    Call AnotherMacro(obj.Chart)
End If
Next obj