Chart with Legend

     

    Download The Workbook

    Sub Column_Chart_Chart_with_Legend()
    On Error Resume Next
    'Define variable for worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Worksheets(Sheets.Count), Count:=1)
    sh.Activate
    sh.Name = "Chart with Legend"
    ActiveWindow.DisplayGridlines = False

    'Copy the data from Datasheet to newly created sheet
    Dim Datasheet As Worksheet
    Set Datasheet = ThisWorkbook.Sheets("Data Sheet")
    Datasheet.Range("B4:C9").Copy sh.Range("B4")

    'Define the Chart Object in worksheet
    Dim ch As ChartObject

    'Define the Measures of chart
    With sh.Range("H3:P20")
    Set ch = sh.ChartObjects.Add( _
    Left:=.Left, _
    Top:=.Top, _
    Width:=.Width, _
    Height:=.Height)
    End With
    'Select chart Property from chart object
    With ch.Chart
    .ChartType = xlColumnClustered
    .SetSourceData sh.Range("B4:C9"), PlotBy:=xlColumns
    'Chart Title
    .HasTitle = True
    .ChartTitle.Text = "Chart with Title and Legend"
    .ChartTitle.Font.ColorIndex = 5
    .ChartTitle.Font.Size = 25
    .ChartTitle.Font.FontStyle = "Footlight MT Light"
    .ChartTitle.Shadow = True
    .HasLegend = True
    .Legend.Position = xlLegendPositionTop
    .Legend.Font.ColorIndex = 5
    .Legend.Font.Size = 15
    .Legend.Font.Bold = True
    '=================Legend of chart=============
    End With
    ch.Name = "Jan Month Sales Report"
    End Sub