Creating dynamic chart legends in Excel VBA

This code ensures that the legend updates automatically based on visible series in a chart.

VBA Code to Create Dynamic Chart Legends

Sub CreateDynamicLegend()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim ser As Series
    Dim legendRange As Range
    Dim legendRow As Integer
    Dim lastRow As Integer
    Dim legendCol As Integer   
    ' Set the worksheet containing the chart
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name accordingly   
    ' Set the chart object - Modify this to match the name of your chart
    Set cht = ws.ChartObjects("Chart 1") ' Adjust chart name if necessary   
    ' Define where the dynamic legend should be placed
    legendRow = 2 ' Start row for legend
    legendCol = 10 ' Column where the legend should appear (e.g., Column J)
    ' Clear previous legend entries
    ws.Range(ws.Cells(legendRow, legendCol), ws.Cells(legendRow + 50, legendCol + 1)).Clear   
    ' Loop through the series collection of the chart
    For Each ser In cht.Chart.SeriesCollection
        If ser.Format.Line.Visible = msoTrue Or ser.Format.Fill.Visible = msoTrue Then
            ' Add series name to the legend
            ws.Cells(legendRow, legendCol).Value = ser.Name           
            ' Set the color next to it
            ws.Cells(legendRow, legendCol + 1).Interior.Color = ser.Format.Line.ForeColor.RGB           
            ' Move to the next row
            legendRow = legendRow + 1
        End If
    Next ser   
    ' Adjust column width for better visualization
    ws.Columns(legendCol).AutoFit   
    ' Notify user
    MsgBox "Dynamic legend updated successfully!", vbInformation, "Legend Update"
End Sub

Detailed Explanation

1. Setting Up the Worksheet and Chart

• The macro starts by referencing the correct worksheet (ws) where the chart is located.
• The chart object (cht) is identified by its name « Chart 1 ». You may need to update this to match your actual chart name.

2. Defining the Legend Location

• The legend’s starting row (legendRow = 2) and column (legendCol = 10, meaning column « J ») are predefined.
• Any previous legend content in that area is cleared.

3. Looping Through Chart Series

• The macro loops through each SeriesCollection in the chart.
• It checks if the series is visible by verifying the line or fill visibility (msoTrue).
• If the series is visible, its name is added to the specified legend column.
• The corresponding color is applied to the adjacent cell.

4. Formatting the Legend

• The AutoFit function adjusts the column width to fit the series names properly.
• A message box (MsgBox) informs the user that the legend has been updated.

How to Use This Macro

1. Ensure your chart is named « Chart 1 » (or update the code accordingly).
2. Place this VBA script in a module in the VBA editor (ALT + F11 → Insert → Module).
3. Run CreateDynamicLegend() to update the legend.