If you are using Excel on a daily basis, in many cases you will need to create some charts in order to present your data/calculation results etc. If you are perfectionist and you need to perfectly align your chart legends – within plot area – then the following lines of code will solve your problems.
I wrote four macros that align the chart legend according to the position of the four corners of plot area. So, by using them you will be able to align the chart legend at the top left, top right, bottom left and bottom right corner of the plot area.
Option Explicit
Sub LegendTopLeft()
'Aligns the chart legend in the top left corner of plot area
'By Christos Samaras
'Test if the selection is a chart
If Not ActiveChart Is Nothing Then
'Show the chart legend and format it
With ActiveChart
.HasLegend = True
With .Legend
With .Border
.Color = vbBlack
.LineStyle = xlContinuous
End With
.Format.Line.Weight = 0.25
.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
End With
'Set the position of the legend
With ActiveChart.Legend
.Left = ActiveChart.PlotArea.InsideLeft - ActiveChart.Axes(xlValue).Format.Line.Weight
.Top = ActiveChart.PlotArea.InsideTop
End With
Else
'If the selection is not a chart a message box appears
MsgBox "Please select a chart!", vbExclamation
End If
End Sub
Sub LegendTopRight()
'Aligns the chart legend in the top right corner of plot area
'By Christos Samaras
'Test if the selection is a chart
If Not ActiveChart Is Nothing Then
'Show the chart legend and format it
With ActiveChart
.HasLegend = True
With .Legend
With .Border
.Color = vbBlack
.LineStyle = xlContinuous
End With
.Format.Line.Weight = 0.25
.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
End With
'Set the position of the legend
With ActiveChart.Legend
.Left = ActiveChart.PlotArea.InsideLeft + ActiveChart.PlotArea.InsideWidth - .Width - 2 * .Format.Line.Weight
.Top = ActiveChart.PlotArea.InsideTop
End With
Else
'If the selection is not a chart a message box appears
MsgBox "Please select a chart!", vbExclamation
End If
End Sub
Sub LegendBottomLeft()
'Aligns the chart legend in the bottom left corner of plot area
'By Christos Samaras
'Test if the selection is a chart
If Not ActiveChart Is Nothing Then
'Show the chart legend and format it
With ActiveChart
.HasLegend = True
With .Legend
With .Border
.Color = vbBlack
.LineStyle = xlContinuous
End With
.Format.Line.Weight = 0.25
.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
End With
'Set the position of the legend
With ActiveChart.Legend
.Left = ActiveChart.PlotArea.InsideLeft - ActiveChart.Axes(xlValue).Format.Line.Weight
.Top = ActiveChart.PlotArea.InsideTop + ActiveChart.PlotArea.InsideHeight - .Height
End With
Else
'If the selection is not a chart a message box appears
MsgBox "Please select a chart!", vbExclamation
End If
End Sub
Sub LegendBottomRight()
'Aligns the chart legend in the bottom right corner of plot area
'By Christos Samaras
'Test if the selection is a chart
If Not ActiveChart Is Nothing Then
'Show the chart legend and format it
With ActiveChart
.HasLegend = True
With .Legend
With .Border
.Color = vbBlack
.LineStyle = xlContinuous
End With
.Format.Line.Weight = 0.25
.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
End With
'Set the position of the legend
With ActiveChart.Legend
.Left = ActiveChart.PlotArea.InsideLeft + ActiveChart.PlotArea.InsideWidth - .Width - 2 * .Format.Line.Weight
.Top = ActiveChart.PlotArea.InsideTop + ActiveChart.PlotArea.InsideHeight - .Height
End With
Else
'If the selection is not a chart a message box appears
MsgBox "Please select a chart!", vbExclamation
End If
End Sub
Sample file
This workbook (see figure) contains a sample chart for testing the above four macros. Apart from aligning the chart legend, the workbook contains a useful tip about how to automatically update chart series without using VBA code. Discover it!
Download it from here
This file can be opened with Office 2007 or newer.
