Stack Column and Shapes in Excel

M

MeetGlen

New Member
Joined
Aug 8, 2011
Messages
21
Hi All,

I am not able to work on the Stack Column Chart in VBA. One main reason i understood from the debug was its not able to reference to particular chart when it becomes active. Help on this would be very much appreciated.

And second aspect is if there is any workaround in working out excel shapes in excel.

Hope i making it understandable.

Thank you for the help.

Regards Glen
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
No, that's not understandable.

A good start would be to show your code, and describe what you intended it to do, and what actually happens.
 
Upvote 0
Re: Stack Column and Shapes in VBA Excel

Thanks for your quick response.

I tried to create a stack column chart.

1. let alone of positioning the bar chart. I am not able to run the VBA creation fully, since each time, the chart is created it takes a new chart name. i am getting the error in the active chart and active sheet.

Creation of the chart (it will be single one, showing two series/types):

A. For the series name

(i). i am refering to a cell (with formula showing a percentage).
(ii) i am refering to a cell (with formula showing a percentage).

B. For the series data -
(i)also refering to another cell which is having calculated data of all the data which is >2

(ii)also refering to another cell which is having calculated data of all the data which is <2

After 'select data'
1. i took off the grid lines in the chart
2. took off all the border lines
3. only filled the data series with two colors (red and green)
4. then added the data labels and formatted the labels to the series name (%) and font in white bold
5. And positioned the single stacked column chart to B5:B31

Debugging takes place after the above 'select data' process.

I do not find the attachment tab to attach my tab. Hence i hope the above gist is okay. Thank you.

Regards, Glen
 
Upvote 0
Hi Glenn,

Greetings of the day.

I just recorded the code and then placed all, not to miss out anything.

i am a novice to VB, but would like to learn it soon. To some extent i learnt to create wks, refer to cell down, and so on...

Please find the code below
Sub tomakeGlobalSummary()
'
' tomakeGlobalSummary Macro
' 08/12/11
'
'
Range("B6").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Regional Dashboard - APJ'!$F$2"
ActiveChart.SeriesCollection(1).Values = "='Regional Dashboard - APJ'!$AA$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Regional Dashboard - APJ'!$F$3"
ActiveChart.SeriesCollection(2).Values = "='Regional Dashboard - APJ'!$AA$3"
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveSheet.ChartObjects("Chart 2").Activate
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
Range("C5").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Global Summary'!$B$3:$E$5")
ActiveChart.ChartType = xlColumnStacked
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Regional Dashboard - EMEA'!$F$2"
ActiveChart.SeriesCollection(1).Values = "='Regional Dashboard - EMEA'!$AA$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Regional Dashboard - EMEA'!$F$3"
ActiveChart.SeriesCollection(2).Values = "='Regional Dashboard - EMEA'!$AA$3"
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Range("D5").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Global Summary'!$B$3:$E$5")
ActiveChart.ChartType = xlColumnStacked
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Regional Dashboard - USPS'!$F$2"
ActiveChart.SeriesCollection(1).Values = "='Regional Dashboard - USPS'!$AA$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Regional Dashboard - USPS'!$F$3"
ActiveChart.SeriesCollection(2).Values = "='Regional Dashboard - USPS'!$AA$3"
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveSheet.ChartObjects("Chart 4").Activate
Range("E6").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Regional Dashboard - AMS'!$F$2"
ActiveChart.SeriesCollection(1).Values = "='Regional Dashboard - AMS'!$AA$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Regional Dashboard - AMS'!$F$3"
ActiveChart.SeriesCollection(2).Values = "='Regional Dashboard - AMS'!$AA$3"
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(2).Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(2).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(2).DataLabels.Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.ChartArea.Select
Range("B3:E3").Select
End Sub

Please find the image in the below link
http://preview.tinyurl.com/3k5bfqt
http://www.mediafire.com/?rv9v6674byzj12d

Thank you for the assistance. Regards Glen
 
Upvote 0
When you create the chart, set an object to be the new chart object and use that, like this:
Code: 
Range("B6").Select
Set mychart = ActiveSheet.Shapes.AddChart
mychart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Regional Dashboard - APJ'!$F$2"
ActiveChart.SeriesCollection(1).Values = "='Regional Dashboard - APJ'!$AA$2"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "='Regional Dashboard - APJ'!$F$3"
ActiveChart.SeriesCollection(2).Values = "='Regional Dashboard - APJ'!$AA$3"
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Axes(xlValue).Select
Selection.Delete
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(2).DataLabels.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowLegendKey = -1
Selection.Separator = "" & Chr(10) & ""

... not that I can see that you need to refer to it more than once in your code, actually.
 
Upvote 0
Thank you Glen, and for your time.

It works as jem.

Would like to request also (1) if there is a way to position the chart to particular range say B5:B31 . (meaning size the chart and place/float it within the said range)
(2) and how to apply color green/red and other formatting like making the plot and chart area transparent.

thanking you in advance.
 
Upvote 0
Have a look at this:
Code: 
mychart.Top = Range("B6").Top
mychart.Left = Range("B6").Left
mychart.Width = Range("B6:G23").Width
mychart.Height = Range("B6:G23").Height
mychart.Chart.PlotArea.Interior.ColorIndex = xlColorIndexNone
mychart.Chart.ChartArea.Interior.ColorIndex = xlColorIndexNone
 
Upvote 0
Thanks Glen. I think i am almost there with your help. i tried to fill the data series (series collection 1 and 2) and also trying to format the data labes (1&2) to white bold font Arial not able. Again i request your help in this regard

And for the positioning is there any specific code to position the data series accurately to the said ranges.

Thanks once again.
 
Upvote 0
What did you try to fill the data series with?

Label fonts are set like:
ActiveChart.SeriesCollection(2).DataLabels.Font.ColorIndex = 2
ActiveChart.SeriesCollection(2).DataLabels.Font.Bold = True

I don't understand your query about positioning ... I've given code to position the chart ... what does "position the series" mean?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
This is driving me NUTS!! I just want to plot a stacked bar next to a regular bar
Replies
1
Views
196
RoryA
RoryA
O
  • Question Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
1K
OrangeJuice
O
N
  • Question Question
Stacked Chart to show value and status YTD
Replies
2
Views
823
neilo1969
N
E
  • Question Question
Filling Modules in a Trapezoid Shape Using Excel VBA
Replies
1
Views
241
Fluff
Fluff
JoeMajestee
  • Solved
How to refer to a shape added to a chart
Replies
2
Views
549
JoeMajestee
JoeMajestee

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top