Thread: graphing a chart in VBA, showing series legends on the chart

Hi guys,

I'm trying to generate a graph in VBA, but am having some difficulties getting the series legend on the graph.

Can anybody help?

Private Sub GraphMe_Click()
    Dim intSeries As Integer
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nColumns As Long
    
    Range(Cells(2, 3), Cells(13, 3)).Name = "datalegends"
    
    dTop = 75      ' top of first row of charts
    dLeft = 300    ' left of first column of charts
    dHeight = 225  ' height of all charts
    dWidth = 375   ' width of all charts
    nColumns = 3   ' number of columns of charts

Application.ScreenUpdating = False
Charts.Add


With ActiveChart
For intSeries = 2 To .SeriesCollection.Count
.SeriesCollection(intSeries).Name = Range("datalegends").Cells(intSeries, 3)
Next


.Axes(xlCategory, xlPrimary).HasTitle = True 'set the switch on first
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlCategory).HasMajorGridlines = True
.Axes(xlValue).HasMajorGridlines = True
.PlotArea.Interior.ColorIndex = 2
.PlotArea.Interior.PatternColorIndex = 1
.HasTitle = True 'set the switch on first
.ChartTitle.Characters.Text = "Capacity"
.HasLegend = True
.ChartType = xlXYScatterLines
.SetSourceData Source:=Sheets("Sheet1").Range("B2:B13")
.Location Where:=xlLocationAsObject, Name:="Sheet1"

End With


ActiveWindow.RangeSelection.Activate
Application.ScreenUpdating = True
End Sub

So I think that for loop is what I want shown on the x-axis on the graph, but months are not showing when run.

Any ideas?

The chart type xy-scatter only displays numeric values on the axes.
If you want months on the x axis you will need to use a Line chart.

Private Sub GraphMe_Click()
    Dim intSeries As Integer
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nColumns As Long
    Dim rngData As Range
    Dim rngLabels As Range
    Dim rngName As Range
    
    With ActiveSheet
        Set rngData = .Range(.Cells(2, 2), .Cells(13, 2))
        Set rngLabels = .Range(.Cells(2, 3), .Cells(13, 3))
        Set rngName = .Cells(1, 2)
    End With
    
    dTop = 75      ' top of first row of charts
    dLeft = 300    ' left of first column of charts
    dHeight = 225  ' height of all charts
    dWidth = 375   ' width of all charts
    nColumns = 3   ' number of columns of charts

Application.ScreenUpdating = False
Charts.Add


With ActiveChart
    .ChartType = xlLineMarkers
    Do While .SeriesCollection.Count > 0
        ' remove excess series
        .SeriesCollection(.SeriesCollection.Count).Delete
    Loop
    With .SeriesCollection.NewSeries
        .Values = rngData
        .XValues = rngLabels
        .Name = rngName
    End With
    
    .Axes(xlCategory, xlPrimary).HasTitle = True 'set the switch on first
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlCategory).HasMajorGridlines = True
    .Axes(xlValue).HasMajorGridlines = True
    .PlotArea.Interior.ColorIndex = 2
    .PlotArea.Interior.PatternColorIndex = 1
    .HasTitle = True 'set the switch on first
    .ChartTitle.Characters.Text = "Capacity"
    .HasLegend = True
    .Location Where:=xlLocationAsObject, Name:=rngData.Parent.Name

End With


ActiveWindow.RangeSelection.Activate
Application.ScreenUpdating = True
End Sub

If you want to have the months in the legend you will need to plot each value as a series rather than a data point in a single series.

If the activecell is in a block of data the add chart will attempt to use that data in the chart. The loop removes any series added by this action.

I mean if the activecell contains data then excel will try and use that cell and the adjacent cells to populate the chart. If the cell is empty the chart will be empty.

Try it. Select an empty cell and press F11 to create a chart.
Now select a cell that has data and press F11.