将图表标题和轴标题添加到分组图表
问题描述:
我有一个问题,试图创建一个宏来将图表标题和轴标题放到我的图表上,我在线查看并尝试了使用ActiveChart.SetElement和ActiveChart.HasTitle = True,但我无法去工作。我怀疑我的问题在于一次创建多个图的事实。我使用的代码如下:将图表标题和轴标题添加到分组图表
Sub Plotting()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets 'Strain vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range(" '" & aSheet.Name & "'!B3:B15000,'"
& aSheet.Name & "'!G3:G15000")
End With
Next
For Each aSheet In ActiveWorkbook.Worksheets 'Stress vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range(" '" & aSheet.Name & "'!B3:B15000,'"
& aSheet.Name & "'!H3:H15000")
End With
Next
For Each aSheet In ActiveWorkbook.Worksheets 'Stress vs Strain
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range(" '" & aSheet.Name & "'!G3:G15000,'"
& aSheet.Name & "'!H3:H15000")
End With
Next
End Sub
我很感激任何帮助,我可以得到。
Domenic解决了初始问题,现在我有工作代码。现在我试图重新调整Y轴标题的方向。我已经试过这样:
For Each aSheet In ActiveWorkbook.Worksheets 'Strain vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range("'" & aSheet.Name &
"'!B3:B15000,'" & aSheet.Name & "'!G3:G15000")
.SetElement msoElementChartTitleAboveChart
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis
.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis
.ChartTitle.Text = "MyChartTitle" 'change the chart title as desired
.Axes(Type:=xlCategory, AxisGroup:=xlPrimary).AxisTitle.Text =
"MyCategoryAxisTitle" 'change the category axis title as desired
.Axes(Type:=xlValue, AxisGroup:=xlPrimary).AxisTitle.Text =
"MyValueAxisTitle" 'change the value axis title as desired
End With
Next
当我运行的代码我得到“运行时错误‘424’:所需的对象,其中,Y轴被命名为被突出显示的行。任何有关我在做什么错误的见解?
For Each aSheet In ActiveWorkbook.Worksheets 'Strain vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range("'" & aSheet.Name &
"'!B3:B15000,'" & aSheet.Name & "'!G3:G15000")
.SetElement msoElementChartTitleAboveChart
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis
.SetElement msoElementPrimaryValueAxisTitleHorizontal
.ChartTitle.Text = "MyChartTitle" 'change the chart title as desired
.Axes(Type:=xlCategory, AxisGroup:=xlPrimary).AxisTitle.Text =
"MyCategoryAxisTitle" 'change the category axis title as desired
.Axes(Type:=xlValue, AxisGroup:=xlPrimary).AxisTitle.Text =
"MyValueAxisTitle" 'change the value axis title as desired
End With
Next
这工作,但轴标题变成水平。
这就是我想要的输出看起来很理想。
答
关于你的第一个...
For Each aSheet In ActiveWorkbook.Worksheets 'Strain vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range("'" & aSheet.Name & "'!B3:B15000,'" & aSheet.Name & "'!G3:G15000")
.SetElement msoElementChartTitleAboveChart 'change the position as desired
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis 'change the position as desired
.SetElement msoElementPrimaryValueAxisTitleVertical 'change the position as desired
.ChartTitle.Text = "MyChartTitle" 'change the chart title as desired
.Axes(Type:=xlCategory, AxisGroup:=xlPrimary).AxisTitle.Text = "MyCategoryAxisTitle" 'change the category axis title as desired
.Axes(Type:=xlValue, AxisGroup:=xlPrimary).AxisTitle.Text = "MyValueAxisTitle" 'change the value axis title as desired
End With
Next
同样的事情将适用于你的其他的。
希望这会有所帮助!
答
我最终的代码更改为:
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets 'Strain vs Time
With aSheet.Shapes.AddChart.Chart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=aSheet.Range("'" & aSheet.Name & "'!B3:B15000,'" & aSheet.Name & "'!G3:G15000")
.SetElement msoElementChartTitleAboveChart 'change the position as desired
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis 'change the position as desired
.SetElement msoElementPrimaryValueAxisTitleRotated 'change the position as desired
.SetElement msoElementLegendNone
.ChartTitle.Text = "Strain vs Time" 'change the chart title as desired
.Axes(Type:=xlCategory, AxisGroup:=xlPrimary).AxisTitle.Text = "Time (sec)" 'change the category axis title as desired
.Axes(Type:=xlValue, AxisGroup:=xlPrimary).AxisTitle.Text = "Strain" 'change the value axis title as desired
End With
Next
它解决了这个问题。感谢您的帮助Domenic
它工作得很好! SetElements必须在被命名前进行定义吗?我尝试了一种类似于前面的方法,但由于某种原因,它不起作用。 – Cyrus
是的,您需要首先设置元素,以使其对应的对象可用。 – Domenic
任何想法如何使轴附近的Y轴标签?我试过这个: – Cyrus