VBA Chart title from multiple cells

[Human_doing]

Hi all,

The following code creates a simple chart using VBA:

Sub Chart()
    Range("H1:I13").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sales").Range("H1:I13"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sales"
End With
End Sub

I have the name of the department (sales) as the name of the worksheet and also in cell A2. The date of the current period is in cell A3 (Apr 10 - Mar 11). I am looking for the title of the chart to be 'Sales data Apr 10 - Mar 11' i.e. cell A2 + 'Sales data' + Cell A3, can anyone please help with the VBA for this?

Thanks

 

[Sektor]

In sheet module.

Sub F()

    Dim cht As Chart
    
    Set cht = Me.ChartObjects(1).Chart
    
    With cht
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Characters.Text = Me.Range("A2") & " Sales data " & Me.Range("A3")
    End With

End Sub

 

[Sektor]

Sorry, you have a chart sheet. Here's fix:

Sub F()

    With Charts("Chart1")
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Characters.Text = Range("A2") & " Sales data " & Range("A3")
    End With

End Sub