|
Why doesn't the code create a column stacked chart?
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 |
Why doesn't the code create a column stacked chart?
You get better results if you overrule the macro recorder and put ChartType
after SetSourceData. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "tskogstrom" wrote in message ups.com... 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 |
Why doesn't the code create a column stacked chart?
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: You get better results if you overrule the macro recorder and put ChartType after SetSourceData. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "tskogstrom" wrote in message ups.com... 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 |
Why doesn't the code create a column stacked chart?
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/ChartsH...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) 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "tskogstrom" wrote in message ups.com... 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: You get better results if you overrule the macro recorder and put ChartType after SetSourceData. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "tskogstrom" wrote in message ups.com... 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 |
| All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com