在 Excel/VBA 中将文本框附加到图表上的点或线
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17582636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Attaching a Textbox to a point or line on a chart in Excel/VBA
提问by hannsta
I was wondering how to attach a textbox to a point or line in an Excel chart for the macro I am working on. I have been using the .AddTextboxmethod such as
我想知道如何将文本框附加到我正在处理的宏的 Excel 图表中的点或线。我一直在使用.AddTextbox诸如
.Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 250, 100, 15) _
.TextFrame.Characters.Text = "Temperature"
But I have to then manually drag the textbox over the line on the chart it is representing as the orientation is of the chart not the line. Is there a way to convert the line/point to a chart orientation which I could use as a variable? or another way? Possibly using the datalabel function, though I want to be able to customize one of the axis locations. Thanks
但是我必须手动将文本框拖到它所代表的图表上的线上，因为方向是图表而不是线。有没有办法将线/点转换为我可以用作变量的图表方向？或其他方式？可能使用 datalabel 函数，但我希望能够自定义轴位置之一。谢谢
采纳答案by Kazimierz Jawor
To solve your question you need to get the left & top position of two objects:
要解决您的问题，您需要获取两个对象的左侧和顶部位置：
- chart itself, which position is set in relation to top-left corner of sheet range area
- point in series which position is set in relation to top-left corner of chart
- 图表本身，相对于工作表范围区域的左上角设置哪个位置
- 系列中相对于图表左上角设置的位置
Combination of both result with the following code (fixed parameters-required changes to your situation, could be more dynamic with loop)
两个结果与以下代码的组合（固定参数 - 需要对您的情况进行更改，使用循环可能会更加动态）
Sub Add_Text_to_point()
Dim tmpCHR As ChartObject
Set tmpCHR = Sheet1.ChartObjects(1) 'put index of your chartobject here
'for first serie, for point 2nd here
'(change accordingly to what you need)
With tmpCHR.Chart.SeriesCollection(1).Points(2)
Sheet1.Shapes.AddTextbox(msoTextOrientationHorizontal, _
.Left + tmpCHR.Left, _
.Top + tmpCHR.Top, _
100, 15) _
.TextFrame.Characters.Text = "Temperature"
End With
End Sub
After result presenting the picture below.
结果呈现下图。
回答by suiluj_julius
Another option would be to use the data labels of Excel. I see two more elegant options:
另一种选择是使用 Excel 的数据标签。我看到了两个更优雅的选择：
- Make a new data series with just one entry in your chart, give the series the coordinates and the name of the label you want to see. Now activate the marker option for the series (if not done already), right-click on the data point, click "add data labels". Now you'll see the y-Value of the point. By right-clicking again and choosing "Format Data Labels" you can change the text to the series name, also the position, the border, etc. are modifiable. Below an example with two data points. You could delete the second point, the line and the marker but like this you see how it works.
- Similarly to the solution from KazJaw you can use the actual data points of your series for attaching custom data labels. This requires some coding, I used this for the chart named "Topview" and wrote percentages next to the data point
- 用图表中的一个条目创建一个新的数据系列，为该系列指定坐标和您想要查看的标签名称。现在激活系列的标记选项（如果尚未完成），右键单击数据点，单击“添加数据标签”。现在您将看到该点的 y 值。通过再次右键单击并选择“格式化数据标签”，您可以将文本更改为系列名称，位置、边框等也可以修改。下面是具有两个数据点的示例。您可以删除第二个点、线和标记，但这样您就可以看到它是如何工作的。
- 与 KazJaw 的解决方案类似，您可以使用系列的实际数据点来附加自定义数据标签。这需要一些编码，我将其用于名为“Topview”的图表并在数据点旁边写下百分比
Sub Add_Text_to_data_points()
percentages(1) = 0.1
percentages(2) = 0.23
'.... further entries
chartNumber = findChartNumber("Topview")
collNumber = 12 ' index of the "points" series
Set tmpCHR = ActiveSheet.ChartObjects(chartNumber)
For i = 1 To tmpCHR.Chart.SeriesCollection(collNumber).Points.count
With tmpCHR.Chart.SeriesCollection(collNumber).Points(i)
If percentages(i) <> 0 Then
.DataLabel.Text = format(percentages(i), "0%")
End If
End With
Next
End Sub
