Cannot get correct type of chart through vba code: xlColumnStacked "variant 2" (i.e. single stacked column for one column of inputcells, each beeing one series)

Question

I cannot assign the correct type of chart through VBA-code, the type I need is "xlColumnStacked variant number 2"

(shown here: https://i.stack.imgur.com/T2fMx.jpg)

The code is run is the following:

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartType = 52 'or synonymously  : ActiveChart.ChartType = xlColumnStacked

This do not occur if the plot is already of the stacked format (still type 52 / xlColumnStacked), either in "bar og column"-stacked mode.

However, if I change the series which are displayed in the plot, the chart goes to "default" and I struggle to find any vba-code wise way to get back to the format which I want (shown here: (https://i.stack.imgur.com/6K1Nz.jpg)).

The thing is that I print these, for 80 series, and I hope to make that automated...

I have had the same problem with changing line style on markers in scatter plot mode too, which made the same changes to the lines (between the markers) in the scatter plott. The problem is that the name of the object or function is overlapping with another name/function in the in-built vba it seems.

This is quite urgent to me, so I will be very grateful for any help!

The question has also been raised here: https://stackoverflow.com/questions/75105952/cannot-get-correct-type-of-chart-xlcolumnstacked-variant-2-through-vba-code

Answer 1

write a loop to copy series 2 data to series 1 . then refresh the chart. All the format of chart will not be changed. Then next series

Answer 2

Thank you,

I was just about to say I found a solution. And so did you.

By not changing the range of series (in vba), and rather substitute the content of the cells in that range, the format will remain the same.

The changing of formatting is completely worked around. Thanks! :D

PS:

It is possible to change in-put range by hand/manually without changing format, as long as the first cell remains unchanged. I.e., by extending or shortening the range, without changing the first cell, the format remains.

It would be cool to know a way of doing that in vba, without stating the whole range, which then causees the change in format...

And also, the problem I menitioned about "lines and marker line type in scatter plot" would also be interesting to know a solution for :)

it is again a problem with same coding in vba for two different things.

Again, thanks! :) Have a nice weekend

Answer 3

Thank you,

I am sorry, but no. I know how to make several charts.

What I do is "recreate" one chart with a new series each time, and take a print of the graph for each series shown.

However, changing series also changes the chart type to "default xlColumnStacked"

and not the "xlColumnStacked - variant" that I need. The graph changes completely format.

Edit: Take a close look on the linked pictures, and you will see "the problem".

Answer 4

Hi Hallvard,

Greetings! Thank you for posting to Microsoft Community.

From your description, you want the VBA to create Stacked Column chart for 80 series.

Have you tried copy the chart which has correct format and use VBA to change the data source?

Test change chart type from other type with VBA, it works without problem.

=================

Sub sss()

ActiveSheet.ChartObjects(1).Activate

ActiveChart.ChartType = xlColumnStacked

End Sub

=================

Best Regards,

Snow Lu