CODEWITHSUNDEEP
vbaexcel
hightides1973
hightides1973

Reputation: 527

Issue with creating stacked column chart

I am trying to create a stacked column chart using excel vba.input data

Following mentioned below is my excel vba code for generating the stacked column chart for the corresponding input data.

      Sub to_draw_chart()
         ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
         ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
         ActiveChart.Axes(xlValue).Select
         ActiveChart.Axes(xlValue).MaximumScale = 1000
         ActiveChart.Axes(xlValue).MajorUnit = 250

         ActiveChart.ChartArea.Select
         ActiveChart.Axes(xlCategory).Select

         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
         ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
         Application.CommandBars("Format Object").Visible = False
         ActiveChart.FullSeriesCollection(2).Select
         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(5).Select
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(12).Select
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(15).Select
         Selection.Format.Fill.Visible = msoFalse

         ActiveChart.ChartArea.Select
         ActiveChart.ChartTitle.Select
         ActiveChart.ChartTitle.Text = "Chart "
         Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart "
      End Sub

outputExpected_VS_output_getting

But when i run this macro, the output i am getting is different. the problem is in the x axis. my x axis should be D0, D1,D2(as you can see in image "Output Expected". But it is different. i have also attached the output i am getting when i run the vba code (Second image).

I am not understanding why my x axis is getting changed which is indeed affecting the code and the output graph.

Manually when i do without using code then i am getting the correct output.

where am i going wrong?

Upvotes: 2

Views: 295

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Not sure what is your desired final chart with the code supplied, since I didn't use some of your code lines.

There is another option, a "cleaner" and more efficient way to handle charts (there is no need to select them all time, I think you did it with the MACRO recorder).

Anyway, see my code below, it gave me the exact result like with the manual step (just like in your attached images).

Option Explicit

Sub to_draw_chart()

Dim Sht1                   As Worksheet

' modify to your sheet name
Set Sht1 = ThisWorkbook.Sheets("Sheet1")

' change Left, Top, Width , Height according to your needs
Sht1.Shapes.AddChart(xlColumnStacked, 200, 200, 500, 500).Select

With ActiveChart
    .SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
    .Axes(xlValue).MaximumScale = 1000
    .Axes(xlValue).MajorUnit = 250

    .HasTitle = True
    .ChartTitle.Text = "Chart "
    .ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "Chart "

    .Axes(xlCategory).CategoryType = xlCategoryScale
    .Axes(xlCategory).CategoryType = xlAutomatic

' not sure what is the purpose with the lines below ?      
'         Application.CommandBars("Format Object").Visible = False
'         ActiveChart.FullSeriesCollection(2).Select
'         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(5).Select
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(12).Select
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(15).Select
'         Selection.Format.Fill.Visible = msoFalse
End With   

End Sub

Upvotes: 1

Sergey Ryabov
Sergey Ryabov

Reputation: 656

You need to Switch Row/Column by adding ActiveChart.PlotBy = xlColumns. The following should solve the issue.

  Sub to_draw_chart()
     ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
     ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
     ActiveChart.PlotBy = xlColumns
     ActiveChart.Axes(xlValue).Select
     ActiveChart.Axes(xlValue).MaximumScale = 1000
     ActiveChart.Axes(xlValue).MajorUnit = 250

     ActiveChart.ChartArea.Select
     ActiveChart.Axes(xlCategory).Select

     ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
     ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
     Application.CommandBars("Format Object").Visible = False
     ActiveChart.FullSeriesCollection(2).Select
     ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(5).Select
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(12).Select
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(15).Select
     Selection.Format.Fill.Visible = msoFalse

     ActiveChart.ChartArea.Select
     ActiveChart.ChartTitle.Select
     ActiveChart.ChartTitle.Text = "Chart "
     Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart "
  End Sub

Upvotes: 0

Related Questions