Multilevel Data Labels for a Graph

[TCopple]

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:

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

 

[PHV]

manually in excel you highlight the whole table and it does it automatically
Have you macro-recorded this manual action ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TCopple]

If I just use the table in excel and I highlight everything, click the graph button, it makes the graph the appropriate way by labeling each bar as both the time period and the type.

When I implement that action in VBA is when it doesn't work.

Tyler

 

[SkipVought]

Hi,

"When I implement that action in VBA..."

What is in the rng object?

Consider assigning the XValues and Values properties of the SeriesCollection object separately.

Skip,
[sub]
[red][/red]
[/sub]