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.



How to do it

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.