CODEWITHSUNDEEP
excelvbacharts
Zacchini
Zacchini

Reputation: 143

VBA chart title to include selected/active cell

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

Upvotes: 0

Views: 589

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Something like this: 

Sub Graphs()

    Dim my_range    As Range, t 

    t = Selection.Cells(1, 1).Value + " - " & ActiveSheet.Name

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

Upvotes: 1

Related Questions