CODEWITHSUNDEEP
excelms-officevba
s_a
s_a

Reputation: 977

access a chart's shape ID - excel vba

Some background first.

  1. Excel allows duplicate names for shapes. That is, you can have both a ChartObject and an oval shape in the same worksheet with exactly the same name. You can also have two charts named both "Chart 2". If you try to reference a shape with a duplicate name, e.g.

    ActiveSheet.Shapes("Dupe").Select,

    excel seems to resort to returning the object with the lowest ID (and the duplicate name).

  2. There is no way (that I know of) of linking an ActiveChart with its corresponding containing shape.

I want to create a function like

function GetAChartsShape(c as chart) as Shape,

but I don't know how. The immediate use for this would be to format the selected chart (since there is no way of globally changing a chart's font). Of course, this could also have other uses.

Upvotes: 1

Views: 3795

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6053

The name of the shape containing an embedded chart (the shape is also the chartobject) is:

activechart.parent.name

or if c is declared a chart:

c.parent.name

But of course you know you don't need to select an object to work on it, so just do what you need to do on

c.parent

which avoids the problem of duplicate names.

Upvotes: 1

Related Questions