Legend Alignment Within The Plot Area Of A Chart

Share this

April 13, 2012

Legend Alignment Within The Plot Area Of A Chart
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.

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>