copy

Friday, 13 April 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.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info