VBA for Excel 2007 tutorial-format chart legend

Format chart legend

After learn VBA code in this example, you will be able to display and format the legend of a chart.  By running the VBA code example, the chart shows up with a legend that is formatted with a CalmWater gradient style.

VBA for Excel: VBA example code to format legend of the chart

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.

VBA code for this example:

Option Explicit
Private Sub Worksheet_Activate()
    Dim i As Integer
    'Fill Source data
    For i = 1 To 10
         Range("A" & i) = Int(100 * Rnd) + 1
         Range("B" & i) = Int(100 * Rnd) + 1
    'Select the data source
    'Create a clusted column chart
    ActiveSheet.Shapes.AddChart xl3DColumn, 10, 180
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$10"), PlotBy:=xlColumns
    'Make sure the lend is shown
    ActiveChart.HasLegend = True
    'Fill the chart legend with CalmWater gradient style
    ActiveChart.Legend.Format.Fill.PresetGradient msoGradientHorizontal, 3, msoGradientCalmWater
End Sub


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.