Search code examples
excelvbaexcel-charts

Name chart title and axes automatically

I used this code to generate graph automatically over selected range of data but i also want to give it a title and names to axes automatically in one click for example i want to name x axis as subsection and y axis as count and i want to give a title to the chart as matrix graph how can i do that. Any help would really mean a lot Thanks in advance,sorry for any inconvenience

Sub create_embedded_chart()
Dim oChartObj As ChartObject

Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=0, Left:=0, Width:=50, Height:=50)
oChartObj.Chart.SetSourceData Sheets("Tracedata").Range("A1:C45")

End Sub

The graph

Solution

  • Please, test the next (updated) code:

    Sub create_embedded_chart()
    Dim oChartObj As ChartObject

    Set oChartObj = ActiveSheet.ChartObjects.Add(top:=0, left:=0, width:=200, height:=200)
    oChartObj.Chart.SetSourceData Sheets("Tracedata").Range ("A1:C45")
    With oChartObj.Chart
        .HasTitle = True
        .chartTitle.text = "Matrix graph"
        With .Axes(xlValue)
            .HasTitle = True
            With .AxisTitle
                .Caption = "Count"
                .Font.Name = "Arial"
                .Font.Size = 10
                .Font.Italic = True
            End With
        End With
        With .Axes(xlCategory)
            .HasTitle = True
            .AxisTitle.text = "Subsection"
        End With
    End With
End Sub

    The chart