PDA

View Full Version : Changed Named Range of Chart



CraigR
08-09-2007, 11:49 AM
I'm creating charts dynamically and passing in the Named Range as the SeriesCollection(1).Values. The named range is one row on a datasheet. The chart is created with no problems, but it does not reference the named range as its source. Instead, it has resolved it to a formula reference of cells.
I also have a combobox that allows the user to change values. In the _change event for the combobox, I'm changing the source of the named range to point to the appropriate row on my datasheet. I thought this would make the chart refresh, but since it is not actually referencing the named range, it does not refresh. If I run the code to create the chart again, it is rebuilt using the new value of the named range, but that seems like excessive overhead.
How do I make my chartobject keep its reference to a named range so that the chart updates when the named range actually changes to a different row?
I've seen examples of using Offset and CountA, but either I didn't understand them (a very high probability) or they didn't seem to really apply. They appeared to be geared toward having your data range just grow and the chart seeing the expanded range. I'm changing the source named range to another part of the sheet.
One other thing is that my charts usually have multiple series (each based off a named range that I need to manipulate as described above.
Any help is appreciated.
Thx.
CraigR

mdmackillop
08-09-2007, 12:07 PM
Hi Craig
Can you post your workbook? Use Manage Attachents in the Go Advanced section.
Regards
MD

CraigR
08-09-2007, 12:36 PM
I deleted all the other charts and their code. The one chart is in the Volume_And_Cost_Per_Package module.
Thx.
CraigR

rory
08-09-2007, 04:07 PM
You can use syntax like this:
.SeriesCollection(1).Values = "='" & WSD.Name & "'!" & RangeName

HTH

CraigR
08-10-2007, 06:55 AM
I had tried this before but I didn't include the equal sign in quotes. This works perfectly.
Thanks.
Craig