CODEWITHSUNDEEP
excelvbacharts
Rik
Rik

Reputation: 1987

set object to currently selected chart in excel

All,

I want to set variable cht to the current chart. I've tried the below code but for some reason it puts an extra "chart " on the name. Is there an alternate way?

Set cht = ActiveSheet.ChartObject(ActiveChart.Name)

This code doesn't work because it can't find the object with that name.

msgbox ActiveChart.Name

yields "chart pvtChart1" when the actual name is "pvtChart1". Is there an alternate way to reference the active chart?

I apologize for not correctly formatting the code due to my computer settings that I can't change. Thanks.

-Rik

Upvotes: 1

Views: 4590

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6073

The Chart is embedded in a shape also known as a ChartObject.

If you want to reference the chart (as I suspect), use this:

Dim cht As Chart
Set cht = ActiveChart

If you want to reference the parent chart object:

Dim chtob As ChartObject
Set chtob = ActiveChart.Parent

The chart is related to the chart object like this:

Dim chtob As ChartObject, cht As Chart
Set chtob = ActiveChart.Parent
Set cht = chtob.Chart

Upvotes: 1

mielk
mielk

Reputation: 3940

This chart appended to the name of chart is the name of worksheet where this chart is placed.

You can retrieve the name of chart like that:

MsgBox ActiveChart.Parent.Name

If you want to set variable cht to the current chart you don't need to refer to the collection of charts. You can do it like that:

Set cht = ActiveChart.Parent

Upvotes: 2

Related Questions