VBA/Excel/Access/Word/Excel/Chart Lengend

Adding a Legend to the Chart

   <source lang="vb">

Sub legend()

   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
   With myChartObject.Legend
       .HasLegend = True
       .Font.Size = 16
       .Font.Name = "Arial"
   End With

End Sub

</source>
   
  

Set Lengend position and ChartArea Interior Color

   <source lang="vb">

Sub FormattingCharts()

   Dim myChart As Chart
   Dim ws As Worksheet
   Dim ax As Axis
   Set ws = ThisWorkbook.Worksheets("Sheet1")
   Set myChart = GetChartByCaption(ws, "GDP")
   If Not myChart Is Nothing Then
       myChart.ChartArea.Interior.Color = vbWhite
       myChart.Legend.Position = xlLegendPositionBottom
   End If
   Set ax = Nothing
   Set myChart = Nothing
   Set ws = Nothing

End Sub 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

</source>