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>