时间戳

首页 > 解决方案 > 使用 VBA 修复带有输入框的 Series 1 标题

excel - 使用 VBA 修复带有输入框的 Series 1 标题

问题描述

如何使用 VBA 中的输入框更改系列 1 的名称？这是我当前的代码，数据始终正确绘制图表，但图例中的标题始终显示为系列 1。

' Set x values with input box
Dim myXCell As Range    
Dim myXSeries As Range    
Dim myXTitle As Range

Set myXTitle = Application.InputBox("Please select the heading of the column which contains your desired X values:", "Select title cell", Type:=8)    
Set myXCell = Application.InputBox("Please select the first cell containing data of your desired data range for X values:", "Select data cell", Type:=8)

Range(myXCell, myXCell.End(xlDown)).Select    
Set myXSeries = Selection    

' Set y values with input box    
Dim myYCell As Range    
Dim myYSeries As Range    
Dim myYTitle As Range

Set myYTitle = Application.InputBox("Please select the heading of the column which contains your desired Y values:", "Select title cell", Type:=8)

Set myYCell = Application.InputBox("Please select the first cell containing data of your desired data range of Y values:", "Select data cell", Type:=8)

Range(myYCell, myYCell.End(xlDown)).Select    
Set myYSeries = Selection

' Create Blank Graph (HUGE)

Dim chartObj As ChartObject    
Dim DataChart As Chart    

Set chartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)    
Set DataChart = chartObj.Chart    
DataChart.ChartType = xlXYScatterSmooth
    
' Adds first data series    
    DataChart.SeriesCollection.NewSeries    
    DataChart.FullSeriesCollection(1).Name = myYTitle    
    DataChart.FullSeriesCollection(1).XValues = myXSeries    
    DataChart.FullSeriesCollection(1).Values = myYSeries

谢谢！

标签： excelvba

解决方案

添加图表时您已经选择了数据，因此 Excel 将有助于绘制该数据 - 每当您通过 VBA 添加图表时，确保在开始添加数据之前删除任何“自动”系列总是一个好主意。

此外，当您使用NewSeries它时，它会返回对该系列的引用，因此您可以直接使用它。

Set chartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)    
Set DataChart = chartObj.Chart    
DataChart.ChartType = xlXYScatterSmooth

'remove any auto-plotted series
Do While DataChart.Seriescollection.count > 0
    DataChart.Seriescollection(1).Delete
Loop
    
'Add first data series    
With DataChart.SeriesCollection.NewSeries    
    .Name = myYTitle    
    .XValues = myXSeries    
    .Values = myYSeries
End with

推荐阅读