Return the chart number of a named chart

[itchyII]

Hello all,

How can I retrieve the chart numbet of a named chart. If I have a chart named 'Public', I want to know what the chart number is.

Or better still, how could I set the source data of a chart using the name of the chart to identify it. I currently have:

wks.ChartObjects(1).Chart.SetSourceData Source:=wks.Range("A2" & x + 1)

but the problem is, it would seem that my chart reference numbers have changed on my worksheet (the workbook is used as a template and is copied and saved frequently, I don't know if that is the problem but I have 18 charts on a sheet and the chart numbers, mysteriousely, are no longer sequential!)

So, I have tried:

wks.ChartObjects("Public").Chart.SetSourceData Source:=wks.Range("A2" & x + 1)

but this doesn't work.

How van I do this?

ItchyII

 

[TonyJollans]

Hi ItchyII,

You can get a chart's number from its name by using the [blue]Index[/blue] property, but I'm not sure it will help you.

There is nothing (obviously) wrong with your statement to reference the chart by name. Can you tell us what error you get?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[itchyII]

Hi Tony,
I think that I may have mislead you. Through my research I see that the term 'name' with respect to a chart refers to the automatically generated chart name/number(eg. Chart 1). The name I am refering to is actually the title I have given the chart, simply because I have not figured out how to set the name (chart number) of a chart myself. The system seems to generate it automatically and I do not have control over it. The error message I receive when I run the code as displayed is "Method 'ChartObjects' of object '_Worksheet' failed". If I was to try to use the 'index' property, I assume that the index refers to the charts on the current sheet and is sequential starting at 1 and incerments in the order that the charts were added to the sheet. This may not help me because the charts have been moved around on the spreadsheet (as have been the sseries data) and are no longer displayed in the oder that they were initially entered. On one of my worksheets, I have tried setting the titles to 1 through 18, in the order that they need to be updated by my function. I was thinking that I could simply increment a variable and retreive the chart number for the chart with that title and perform my update.

Any ideas?

ItchyII

 

[SkipVought]

Please post the code that you are having trouble with.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TonyJollans]

Hi ItchyII,

There's no direct way to translate the chart title into an index number - indeed it need not be unique.

To set a chart name - and this is so obvious I can't imagine how you can have missed it ...

Click somewhere else on the worksheet (to de-select the chart)
Press and hold Ctrl
Click on the Chart to select it
The Name is now in the Name Box (top left of the worksheet) and you can overtype it

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[itchyII]

Hi Tony....You have to be kidding! It cannot be that easy! Well, this wouldn't be the first time I could be accused of 'over analysing' the problem! I thank you Tony, for not making fun of me!

humbly...

ItchyII