1

New to VBA and not sure how to set the Chart title to a specific selected column. I am creating multiple graphs from one data sheet so want to be able to select the data and the graph to be named appropriately. 

Sub Charter()

    Dim my_range    As Range

    Set my_range = Selection
    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

    Cells(1, 1).Select

End Sub

I want the graph title to use the data set name that is used in ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter

Thanks

Zacchini
  • 143
  • 13

1 Answers1

0

ActiveChart.ChartTitle.Text is the property needed for the text. This is a way to refer it in the ActiveChart:

ActiveChart.ChartTitle.Text = ActiveSheet.Cells(1, 1).Text

If the name of the FullSeriesCollection(1) is to be used, then:

ActiveChart.ChartTitle.Text = ActiveChart.FullSeriesCollection(1).Name

(in general, avoid working with Select and ActiveChart in VBA - How to avoid using Select in Excel VBA)

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • thank you, but do you know how to set it so that the chart title is taken from the data used in `ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter` – Zacchini Dec 29 '19 at 23:34
  • @ZacEpstein - I miss what you mean by "the data used in `ActiveChart.FullSeriesCollection(1).ChartType = xlXYScatter` `xlXYScatter` is the ChartType of the collection, it is no data. – Vityata Dec 29 '19 at 23:43
  • I want the chart title to be the third column of data that I select, the same data that is used in the scatterplot name from the chart legend – Zacchini Dec 30 '19 at 00:03
  • I want it to have the same name as series 2 from the legend – Zacchini Dec 30 '19 at 00:22
  • @Zacchini - `ActiveChart.ChartTitle.Text = ActiveChart.FullSeriesCollection(1).Name` this should work – Vityata Dec 30 '19 at 06:07