I have a table of information displayed as such:
Last Month Last Quarter
Calls Meetings Calls Meetings
Series 2 1 1 6 3
Series 3 2 9 4 10
Series 1 0 0 2 12
Hopefully that's not to hard to read. What I want is a graph which labels X the axis in groups of Last Month and Last Quarter, but then with the calls and meetings labels under the appropriate bar. To do this manually in excel you highlight the whole table and it does it automatically. That's the implementation I have in my code, but for some reason it treats the row with the calls and meetings labels as a data series and not as a label.
Here is the code:
Just take my word that the range passed in is exactly what I said it was. Column 1 row 1 to column 5 row 5.
Thanks
Tyler Copple
Last Month Last Quarter
Calls Meetings Calls Meetings
Series 2 1 1 6 3
Series 3 2 9 4 10
Series 1 0 0 2 12
Hopefully that's not to hard to read. What I want is a graph which labels X the axis in groups of Last Month and Last Quarter, but then with the calls and meetings labels under the appropriate bar. To do this manually in excel you highlight the whole table and it does it automatically. That's the implementation I have in my code, but for some reason it treats the row with the calls and meetings labels as a data series and not as a label.
Here is the code:
Code:
Private Sub CreateSummaryPercent(ByRef ws As Excel.Worksheet, ByVal rng As Excel.Range)
'Define the necessary variables
Dim chtBar100 As Excel.Chart
Dim WSCharts As Excel.ChartObjects = CType(ws.ChartObjects(), Excel.ChartObjects)
Dim NewWSChart As Excel.ChartObject
'Set the location and size variables
Dim dblTop As Double = 175
Dim dblLeft As Double = 50
Dim dblWidth As Double = 360
Dim dblHeight As Double = 250
'Add the chart
NewWSChart = WSCharts.Add(dblLeft, dblTop, dblWidth, dblHeight)
chtBar100 = NewWSChart.Chart
With chtBar100
.ChartType = Excel.XlChartType.xlColumnStacked100
.SeriesCollection.Add(rng)
.HasTitle = True
.ChartTitle.Characters.Text = "Call/History Breakdown by Division"
.HasLegend = True
.Legend.Position = XlLegendPosition.xlLegendPositionBottom
With .ChartArea.Font
.Name = "Times New Roman"
.Size = 12
End With
'Plot Area
With .PlotArea.Fill
' .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
' .TwoColorGradient(5, 1)
.ForeColor.SchemeColor = 2
.BackColor.SchemeColor = 2
End With
End With
'edit tick labels
Dim chartProperties As Excel.Axis
chartProperties = chtBar100.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary)
chartProperties.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow
With chartProperties.TickLabels
.Orientation = 45
.Font.Size = 9
End With
End Sub
Just take my word that the range passed in is exactly what I said it was. Column 1 row 1 to column 5 row 5.
Thanks
Tyler Copple