Stack Column and Shapes in Excel

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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,177
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.
 

MeetGlen

New Member
Joined
Aug 8, 2011
Messages
21
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
 

MeetGlen

New Member
Joined
Aug 8, 2011
Messages
21
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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,177
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.
 

MeetGlen

New Member
Joined
Aug 8, 2011
Messages
21
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,177
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
 

MeetGlen

New Member
Joined
Aug 8, 2011
Messages
21
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,177
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?
 

Forum statistics

Threads
1,081,479
Messages
5,358,928
Members
400,515
Latest member
Finagill

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top