(Solved) – VBA chart title to include selected/active cell

  • by

I’ve made a macro so that I can select two data columns to create a chart. I want the chart title to include the data column title (row 1) from the first column I have selected, but as I use the macro on multiple data columns within a sheet, it needs to automatically update the title from the appropriate (selected) column of data.

Sub Graphs()

    Dim my_range    As Range

    Set my_range = Union(Selection, ActiveSheet.Range("A:A"))
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.SetSourceData Source:=my_range
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = Cells(1, 1).Value   " - "   ActiveSheet.Name
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Graphs"

End Sub

I have managed to make the title named from a specific cell (1, 6 in this example):
ActiveChart.ChartTitle.Text = Cells(1, 6).Value " - " ActiveSheet.Name
But I want it to change to the cell from the column of data I have selected

Leave a Reply

Your email address will not be published. Required fields are marked *