Create dynamic chart titles using VBA in Excel

This process allows you to modify chart titles based on data or conditions dynamically. I’ll guide you step by step.

Step 1: Open Excel and Access the VBA Editor

1. Open your Excel workbook.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
3. In the VBA editor, you will see a project explorer on the left side. This is where your workbook and its objects are listed.

Step 2: Insert a Module

1. In the VBA editor, right-click on VBAProject (Your Workbook Name).
2. Select Insert > Module. This creates a new module where you can write the VBA code.

Step 3: Write the VBA Code

In the newly inserted module, write the following VBA code. This example assumes that the data you’re working with is in the range A1:B10 and that you’re creating a chart based on this data. The chart’s title will change dynamically based on the contents of a specific cell.

Example Code:

Sub CreateDynamicChartTitle()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dynamicTitle As String
    Dim dataRange As Range   
    ' Set your worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define your data range (change as per your data)
    Set dataRange = ws.Range("A1:B10")   
    ' Create a chart based on the data range
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    chartObj.Chart.SetSourceData Source:=dataRange   
    ' Define the dynamic title - Here we are using data in cell C1 as the dynamic title
    dynamicTitle = ws.Range("C1").Value   
    ' Set the dynamic title to the chart
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Sales Report: " & dynamicTitle   
    ' Optional: Format the chart title (change as needed)
    With chartObj.Chart.ChartTitle.Format.TextFrame2.TextRange
        .Font.Size = 14
        .Font.Bold = True
        .Font.Name = "Arial"
    End With
End Sub

Explanation of the Code:

1. Set the Worksheet and Data Range:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 ») specifies the worksheet where the data resides.
   • Set dataRange = ws.Range(« A1:B10 ») sets the data range for your chart.
2. Create the Chart:
   • Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225) creates a new chart on the sheet at the specified position and size.
   • chartObj.Chart.SetSourceData Source:=dataRange sets the data range for the chart.
3. Dynamic Title:
   • dynamicTitle = ws.Range(« C1 »).Value retrieves the value from cell C1 to use as the dynamic part of the chart title.
   • chartObj.Chart.ChartTitle.Text = « Sales Report:  » & dynamicTitle assigns a title to the chart using the value from C1.
4. Optional Formatting:
   • You can customize the appearance of the chart title using ChartTitle.Format.TextFrame2.TextRange. In the example, the font size is set to 14, bold is enabled, and the font is set to Arial.

Step 4: Run the Macro

1. Close the VBA editor and return to Excel.
2. Press Alt + F8 to open the Macro dialog.
3. Select the macro CreateDynamicChartTitle and click Run.

Output:

• A new chart will appear on your sheet, and its title will dynamically reflect the value in cell C1 (e.g., “Sales Report: 2025 Q1” if C1 contains “2025 Q1”).
• You can change the value in cell C1, and then rerun the macro to update the chart title accordingly.

Conclusion:

By using this approach, you can create dynamic chart titles that change based on the contents of a cell or other conditions in your worksheet. This can be particularly useful when you have multiple charts that need to be updated automatically based on changing data or parameters.