Using VBA to set legend entry titles to series titles

Question

Using VBA, I'm hoping to rename legend entries to their corresponding series titles. In the included picture, I want "Series 1" and "Series 2" to be replaced with "pbx" and "alt". I recorded a macro to learn how to do individually but would like to create a loop to avoid specifically referencing each series title (e.g. If I added a series title at D1 and E1, the legend would automatically include the new titles.

ActiveChart.FullSeriesCollection(1).Name = "=Sheet1!$B$1"

ActiveChart.FullSeriesCollection(2).Name = "=Sheet1!$C$1"

score 1 · Accepted Answer · edited Dec 31 '19 at 15:10

1

We will need more information. Specifically:

Is the range of headers dynamic? Will there always be two?
Will the range of headers always be positioned at B1, C1?
Will there be more than one sheet, or will you only be working in this sheet?
Will there be multiple charts?

As an aside, in doing my due diligence to the VBA community, you should avoid using ActiveChart or really anything dependent on what's "active", including Selection.

How to avoid using Select in Excel VBA

Based on comment:

    Dim rng as Range
    Dim i as Long
    Set rng = ThisWorkbook.Worksheets(1).Range("b1", Range("b1").End(xlToRight))
    i = 0
    For Each Cell in rng.Cells
        i = i + 1
        ActiveChart.FullSeriesCollection(i).Name = Cell.Text
    Next Cell

edited Dec 31 '19 at 15:10

Alex Elfont

115
1
12

answered Dec 31 '19 at 00:56

jclasley

668
5
14

ActiveChart is just from the recorded macro. I'm using a variable to reference the chart. There will not always be two headers. The range of headers will always be positioned at B1, C1, etc. There will only be one chart. There will only be one sheet. – Alex Elfont Dec 31 '19 at 01:45
Create a range defining the header locations. I'll do so with the example you provided, and add an edit to my comment since I don't know how to format the comment – jclasley Dec 31 '19 at 01:47
I had to declare "Dim Cell As Range" to get this to compile. Using the provided code, the legend entries were renamed to "1" and "2", so I don't think the cells B1, C1 were referenced. – Alex Elfont Dec 31 '19 at 14:07
Got it to work! I edited your original post with the accepted answer. – Alex Elfont Dec 31 '19 at 14:46
If I'm writing a routine that a user will apply to an active chart, then ActiveChart is exactly the right way to reference the chart. – Jon Peltier Jan 14 '20 at 03:34

score 0 · Answer 2 · answered Jan 14 '20 at 03:18

So let's write a little procedure you can use for any general chart.

Assume: if Y values are in columns, the name is in the cell above the first Y value; if Y values are in rows, the name is in the cell to the left of the first Y value.

Approach: loop through each series in the active chart, extract the Y values address from the series formula, determine which cell has the name, and apply it to the series.

Sub ApplyNamesToSeries()
  If Not ActiveChart Is Nothing Then
    With ActiveChart
      Dim srs As Series
      For Each srs In .SeriesCollection
        ' series formula
        Dim sFmla As String
        sFmla = srs.Formula
        ' just the arguments
        sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
        ' split into an array
        Dim vFmla As Variant
        vFmla = Split(sFmla, ",")
        ' Y values are the 3rd argument
        Dim sYVals As String
        sYVals = vFmla(LBound(vFmla) + 2)
        ' the Y value range
        Dim rYVals As Range
        Set rYVals = Range(sYVals)
        ' by row or column?
        If rYVals.Rows.Count > 1 Then
          ' by column, so use cell above column of Y values
          Dim rName As Range
          Set rName = rYVals.Offset(-1).Resize(1)
        ElseIf rYVals.Columns.Count > 1 Then
          ' by row, so use cell to left of Y values
          Set rName = rYVals.Offset(, -1).Resize(, 1)
        Else
          ' one cell only: who knows?
          Set rName = Nothing
        End If
        If Not rName Is Nothing Then
          srs.Name = "=" & rName.Address(, , , True)
        End If
      Next
    End With
  End If
End Sub

score 0 · Answer 3 · answered Jan 14 '20 at 03:31

A different approach is to pop up an input box so you can select a range that contains series names for the active chart, then loop through the series and the range, applying each cell to the name of each series.

Sub SelectRangeAndApplySeriesNames()
  If Not ActiveChart Is Nothing Then
    Dim rNames As Range
    On Error Resume Next
    Set rNames = Application.InputBox( _
        "Select a range that contains series names for your chart.", _
        "Select Range of names", , , , , , 8)
    If Not rNames Is Nothing Then
      ' if a range was selected
      If rNames.Rows.Count = 1 Or rNames.Columns.Count = 1 Then
        ' ignore complications...
        With ActiveChart
        Dim iSrs As Long
        For iSrs = 1 To .SeriesCollection.Count
          If iSrs <= rNames.Cells.Count Then
            .SeriesCollection(iSrs).Name = _
                "=" & rNames.Cells(iSrs).Address(, , , True)
          End If
        Next
        End With
      End If
    End If
  End If
End Sub

Using VBA to set legend entry titles to series titles

3 Answers3