使用VBA修复带有输入框的Series 1标题 [英] Use VBA to fix Series 1 title with input box

问题描述

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

How can I change the name of Series 1 using an input box in VBA? This is my current code, and the data always graphs correctly, but the title in the legend always shows up as Series 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

谢谢！

推荐答案

添加图表时已经选择了数据，因此Excel会很有帮助地绘制这些数据-每当通过VBA添加图表时，总是一个好主意确保任何自动"的开始添加数据之前，先删除系列.

You have data already selected when you add the chart, so Excel will helpfully plot that data - whenever you add a chart via VBA it's always a good idea to make sure any "automatic" series are removed before you start adding data.

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

Also when you use NewSeries it will return a reference to the series, so you can use that directly.

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

这篇关于使用VBA修复带有输入框的Series 1标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！