Why doesn't the code create a column stacked chart?

  • Thread starter Thread starter tskogstrom
  • Start date Start date
T

tskogstrom

Hi again...im sorry to ask for help so much, but charts vba is tricky
...

I try to create a column stacked chart of three bars containing each
three different values, much like Andy Pope's
http://andypope.info/charts/StackColTotal.htm

The CHT_21QSBA_S, CHT_21QSBA_B and CHT_21QSBA_A ranges are each three
different cells (name CHT_21QSBA_S = A1,B5,Q3) and I thought this
together with stating charttype=xlcolumnstacked should create a stacked
chart - but it make 9 bars instead...

Why?

....
Set cht = ActiveSheet.ChartObjects("R_Q_" &
Sheet1.Range("RAPP_TILLF").Value & "_21").Chart
With cht
.ChartType = xlColumnStacked
.SetSourceData Union(Sheet2.Range("CHT_21QSBA_S"),
Sheet2.Range("CHT_21QSBA_B"), Sheet2.Range("CHT_21QSBA_A"))
PlotBy = xlRows
.HasTitle = True
.ChartTitle.Characters.Text = chtTitle
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
'.ChartType = xlColumnStacked
End With

cht.SeriesCollection(1).Name = "Name1"
cht.SeriesCollection(2).Name = "Name2"
cht.SeriesCollection(3).Name = "Name3"
....
Please help,
Kind regards
tskogstrom
 
You get better results if you overrule the macro recorder and put ChartType
after SetSourceData.

- Jon
 
Hi Jon,
Still same problem after moved ChartType. Has a stacked column chart
one- or three seriescollections? I ask, because when I later in the
code try to name the SeriesCollection(2) I get runtime 1004 error ...

Is it maybe problem to setsourcedata to not only three union named
ranges that also are each defined three different cells? Like "union on
union"? But I thougt that was the way of defind setsourcerange to
stacked columns to tell it to know each bar should contain three
values?

How should you write the code to create a chart lik the on I describe?

Kind regards
tskogstrom



Jon Peltier skrev:
 
If the range doesn't contain valid data, some chart types will complain when
you access certain properties of the SeriesCollection object. Your Union may
just be asking for trouble. Also, sometimes Excel parses the range
differently than you'd expect or want when you use SetSourceData. I usually
skip SetSourceData; I add each series separately and define its component
parts independent of other series. See

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#addseries

However, I think your problem is more fundamental than that. Looking back on
your original post:
(name CHT_21QSBA_S = A1,B5,Q3)
Click to expand...

You're just begging for trouble. Excel needs nicely shaped ranges for chart
source data. You'd be better off with an unused worksheet range, say
AA1:AA3, with links in these cells to those you tried to associate using the
defined name. Then either assign the name to this range, or simply use the
range as is.

Then combine this nicer data arrangement with my series-by-series chart
building approach above, and explicitly tell Excel which range to use for
the name, values, and xvalues of each series.

- Jon
 
Back
Top