Great solution! Thank you so much.
Now can you please tell me what I have to do to display the range name used
to create a chart data series via VBA?
If I create 2 named ranges through the user interface
[eg. 'Sheet1!Dates' refers to
'=Offset(Sheet1!$A$2,0,0,CountA(Sheet1!$A:$A),1)'
and 'Sheet1!Prices' refers to
'=Offset(Sheet1!$B$2,0,0,CountA(Sheet1!$B:$B),1)']
and then enter those names in the Series dialog box under Chart/SourceData
[eg. Series1 Category Labels refers to '=Sheet1!Dates'
and Series1 Values refers to '=Sheet1!Prices',
Excel retains the range names after the dialog closes,
so anyone looking at the chart can tell at a glance where the series came
from.
If I try to do the same thing through VBA, I can use the range name in my
code
[eg. Charts("Chart1").SeriesCollection(1).Values =
Sheets("Sheet1").Range("Prices")],
but the Values field of the Series dialog box ends up displaying
'=Sheet1!$B$2:$B$535'.
Not so self-explanatory.
Once again, what am I doing wrong?
(I'm using XL2000 on WinXP Pro and the chart is in the same workbook but is
not embedded.
Dave Peterson said:
Another way to see it is to use Jan Karel Pieterse's (with Charles Williams and
Matthew Henson) Name Manager
You can find it at:
NameManager.Zip from
http://www.oaltd.co.uk/mvp
It's useful for lots o'stuff!
I use dynamic range names quite a bit, and although they appear
correctly in the Names list accessed via Insert/Names/Define [eg.
=Offset(Sheet1!$B$2,0,0, CountA(Sheet1!$B:$B),1)], I can't get them to
appear in the NameBox dropdown list. This applies whether I create
them as workbook (aka global) range names or worksheet (aka local)
range names.
What am I doing wrong? Thanks in advance.
