VBA/Excel/Access/Word/Excel/Chart Title

Adding a Chart Title

   <source lang="vb">

Sub chartTitle()

   Dim myChartObject As ChartObject
   Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
       Width:=400, Height:=300)
   
   myChartObject.Chart.SetSourceData Source:= _
       ActiveWorkbook.Sheets("Chart Data").Range("A1:E5")
   
   myChartObject.SeriesCollection.Add Source:=ActiveSheet.Range("C4:K4"), Rowcol:=xlRows
   myChartObject.SeriesCollection.NewSeries
   myChartObject.HasTitle = True

End Sub

</source>
   
  

Seaching for Charts Using the Chart Title

   <source lang="vb">

Function GetChartByCaption(ws As Worksheet, sCaption As String) As Chart

   Dim myChart As ChartObject
   Dim myChart As Chart
   Dim sTitle As String
   Set myChart = Nothing
   For Each myChart In ws.ChartObjects
       If myChart.Chart.HasTitle Then
           sTitle = myChart.Chart.ChartTitle.Caption
           If StrComp(sTitle, sCaption, vbTextCompare) = 0 Then
               Set myChart = myChart.Chart
               Exit For
           End If
       End If
   Next
   Set GetChartByCaption = myChart
   Set myChart = Nothing
   Set myChart = Nothing

End Function Sub TestGetChartByCaption()

   Dim myChart As Chart
   Dim ws As Worksheet
   Set ws = ThisWorkbook.Worksheets("Sheet1")
   Set myChart = GetChartByCaption(ws, "I am the Chart Title")
   If Not myChart Is Nothing Then
       Debug.Print "Found chart"
   Else
       Debug.Print "Sorry - chart not found"
   End If
   Set ws = Nothing
   Set myChart = Nothing

End Sub

</source>
   
  

To change chart default title, set the Text property of the ChartTitle object, which represents the chart title

   <source lang="vb">

Sub charTitleText()

   ActiveChart.ChartTitle.Text = "Industrial Disease in North Dakota"

End Sub

</source>
   
  

To format the text of the title, work with its Font object

   <source lang="vb">

Sub format()

   Dim myChartObject As ChartObject
   Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
       Width:=400, Height:=300)
   
   myChartObject.Chart.SetSourceData Source:= _
       ActiveWorkbook.Sheets("Chart Data").Range("A1:E5")
   
   myChartObject.SeriesCollection.Add Source:=ActiveSheet.Range("C4:K4"), Rowcol:=xlRows
   myChartObject.SeriesCollection.NewSeries
   myChartObject.HasTitle = True
   
   myChartObject.ChartTitle.Font.Name = "Times"

End Sub

</source>
   
  

To position the title, set its Top property (specifying the number of points from the top edge of the worksheet) and its Left property (specifying the number of points from the left edge of the worksheet)

   <source lang="vb">

Sub source()

   Dim myChartObject As ChartObject
   Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
       Width:=400, Height:=300)
   
   myChartObject.Chart.SetSourceData Source:= _
       ActiveWorkbook.Sheets("Chart Data").Range("A1:E5")
   
   myChartObject.SeriesCollection.Add Source:=ActiveSheet.Range("C4:K4"), Rowcol:=xlRows
   myChartObject.SeriesCollection.NewSeries
   myChartObject.HasTitle = True
   With myChartObject.ChartTitle
       .Top = 100
       .Left = 150
   End With

End Sub

</source>