Loop through one or multiple selected Chart Objects

Question

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

So before trying the For Each, test if Selection is already a ChartArea? — GSerg
– GSerg, Commented Feb 3, 2023 at 13:05
I would be very wary of using Selection with Charts. It's too easy to accidentally select an element of a chart, instead of the whole thing. — CLR
– CLR, Commented Feb 3, 2023 at 14:30

FaneDuru · Accepted Answer · 2023-02-03 14:38:15Z

1

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

edited Feb 3, 2023 at 14:38

answered Feb 3, 2023 at 14:24

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

CLR Over a year ago

Nice, you beat me to it.

FaneDuru Over a year ago

@CLR Not really... I adapted the code. I missed the case of selecting a single object not being a chart. Now, it covers this aspect, too (I think...). :)

GSerg Over a year ago

Why do people insist on the fuzzy TypeName(o) = "obj" instead of the strict TypeOf o Is obj?..

FaneDuru Over a year ago

@GSerg I do not know why people proceed in that way, but i do that because, in cases you cannot be sure about the returned variable type you can simple check it using Debug.print TypeName(Selection), or TypeName(obj). Otherwise, it is possible to use TypeOf, but in order to get the type you should use Debug.Print TypeOf Selection is DrawingObjects, followed by Debug.Print TypeOf Selection is ChartArea, if you have clear expectations related to all possible involved variables type. In the first case the type is simple returned even if you do not know anything about it.

FaneDuru Over a year ago

@GSerg I use TypeOf only in case of iteration between controls on a form, using something as If TypeOf control is MsForms.ComboBox Then` do something...

|

Collectives™ on Stack Overflow

Loop through one or multiple selected Chart Objects

1 Answer 1

23 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

23 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related