Chart title does not update-CodePudding

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = TRUE
    
    If Target.Address(0, 0) = "B6" Then
        Application.EnableEvents = FALSE
        
        If Target.Value = "Yes" Then
            Call GraphOn
        Else
            Call GraphOff
        End If

        Application.EnableEvents = TRUE
        
    End If
    
End Sub

Where the modules GraphOn and GraphOff are defined as follows:

Option Explicit

Sub GraphOn()
    
    Sheet1.Activate
    
    If ActiveSheet.ChartObjects.Count > 0 Then
        Sheet1.ChartObjects.Delete
    End If
    
    Sheet1.Range("H2:L49").Select
    
    Sheet1.Shapes.AddChart2(227, xlLine).Select
    
    With Sheet1.ChartObjects(1).Chart
        ' remove default chart title
        .HasTitle = False
        .HasTitle = True
        .ChartTitle.Caption = "PD Graphs: " & Sheet1.Cells(2, 2).Value
    
    End With
    
End Sub

and

Option Explicit

    Sub GraphOff()
        
        Sheet1.Activate
        
        If ActiveSheet.ChartObjects.Count > 0 Then
            Sheet1.ChartObjects.Delete
        End If
        
    End Sub

Cell B6 is a dropdown list which when set to "Yes" it will produce a graph with the title "PD Graphs: ", plus whatever is in cell B2 (B2 is also a dropdown list). When I select an item from the dropdown list in B2 and select B6 to be "Yes", I get the correct graph with the correct title. However, when I change B2, although the graph correctly changes, the title does not update and shows the previous title.

I have found the following link, which seems to be relevant to my issue, but I cannot get it to work.

CodePudding user response：

You can use a cell reference in a chart title, so likely the easiest way to do this would be to link your title to a cell - see eg https://trumpexcel.com/dynamic-chart-titles-in-excel/

ActiveSheet.ChartObjects(1).Chart.ChartTitle.Caption = "=Test!R4C2"

Note cell address is in R1C1 format: you can set up a cell to contain the full title, then link to that.