VBA图表标题以包括选定/活动单元格

Question

我做了一个宏，这样我就可以选择两个数据列来创建图表。我希望图表标题包含我选择的第一列中的数据列标题(第 1 行(，但是当我在工作表中的多个数据列上使用宏时，它需要自动更新相应(选定(数据列的标题。

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

我设法使标题从特定单元格(在本例中为 1、6(命名：ActiveChart.ChartTitle.Text = Cells(1, 6).Value + " - " + ActiveSheet.Name但是我希望它从我选择的数据列更改为单元格

Answer 1

像这样：

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