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

Adding a Legend to the Chart

 
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

Set Lengend position and ChartArea Interior Color

 
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