CODEWITHSUNDEEP
excelvba
NoNameNo123
NoNameNo123

Reputation: 665

VBA: Telling two charts with the same name apart?

I'm working on a file that has gone through lots of hands. One of the charts I need is named "Dummy_1"; unfortunately, another chart has the same name. So ws.ChartObjects("Dummy_1").Copy gives me the wrong one.

Is there a way to differentiate between the two? Or in general, is there a way to target a chart other than by name? I know I can select it by index, but that's not really what I would call "targeted". Is there the possibility to pick the chart that is closest to a certain cell?

Upvotes: 0

Views: 505

Answers (1)

Cyril
Cyril

Reputation: 6829

You can determine which chart is closest to a target location such that:

Dim ch As ChartObject, chr As Long, chc As Long, r As Long, c As Long, chin As Long, locr As Long, locc As Long
r = 24  'row 24, target
c = 11  'column k, target
For Each ch In ActiveSheet.ChartObjects
    chr = ch.TopLeftCell.Row
    chc = ch.TopLeftCell.Column
    If Sqr((r - chr) ^ 2 + (c - chc) ^ 2) < Sqr((r - locr) ^ 2 + (c - locc) ^ 2) Or locr = 0 Then
        locr = chr
        locc = chc
        chin = ch.Index
    End If
Next ch
Debug.Print chin

You can use the chart index (chin) to copy such that:

activesheets.chartobjects(chin).activate
activechart.parent.copy

Upvotes: 1

Related Questions