I'm very new to doing charts with macros. I'm trying to replicate the Peltier chart with coloured quadrant (using stacked column) and then overlayed with a scatter chart
http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html
I initially recorded a macro while doing this but based on what I have seen on the forums the code is next to useless....giving many runtime errors.
I have managed to get the first part, the colored quadrants going but am running into problems once I try to overlay the scatter chart. I've tried many methods but I can't find a way to address the current chart and then change the chart type to scatter without wiping out the column graph or getting various runtime errors.
Here is the code fragment I'm struggling with .. there are probably many problems
Set chtObjs = CSD.ChartObjects
Set myChtObjs = CSD.ChartObjects ' DON"T KNOW WHAT TO DO HERE?
With myChtObjs.Chart ' GIVES ERRORS HERE
' define chart data range for the row (record)
Set rngChtData = WSD.Range("C2" & ":C" & no_vertices + 1)
Set rngChtXVal = WSD.Range("B2" & ":B" & no_vertices + 1)
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.HasTitle = True
.ChartTitle.Characters.Text = "Demand Matrix"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "No. of Nominations"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% Connections External to Group"
.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = False
.Axes(xlValue).HasMinorGridlines = False
.Axes(xlValue).MaximumScale = 100
.HasLegend = False
.Parent.name = "Demand Matrix"
.name = "Demand Matrix"
.MarkerStyle = 5
.MarkerSize = 7
End With
End With
http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html
I initially recorded a macro while doing this but based on what I have seen on the forums the code is next to useless....giving many runtime errors.
I have managed to get the first part, the colored quadrants going but am running into problems once I try to overlay the scatter chart. I've tried many methods but I can't find a way to address the current chart and then change the chart type to scatter without wiping out the column graph or getting various runtime errors.
Here is the code fragment I'm struggling with .. there are probably many problems
Set chtObjs = CSD.ChartObjects
Set myChtObjs = CSD.ChartObjects ' DON"T KNOW WHAT TO DO HERE?
With myChtObjs.Chart ' GIVES ERRORS HERE
' define chart data range for the row (record)
Set rngChtData = WSD.Range("C2" & ":C" & no_vertices + 1)
Set rngChtXVal = WSD.Range("B2" & ":B" & no_vertices + 1)
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.HasTitle = True
.ChartTitle.Characters.Text = "Demand Matrix"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "No. of Nominations"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% Connections External to Group"
.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = False
.Axes(xlValue).HasMinorGridlines = False
.Axes(xlValue).MaximumScale = 100
.HasLegend = False
.Parent.name = "Demand Matrix"
.name = "Demand Matrix"
.MarkerStyle = 5
.MarkerSize = 7
End With
End With