vba 将悬停标签添加到散点图，使其数据范围在 Excel 2007 中动态更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11495461/
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
Add hover labels to a scatter chart that has it's data range updated dynamically in Excel 2007
提问by Pricey
Hi I want to add labels to the plotted points on a scatter chart in Excel, however my charts data set range changes whenever my macro updates it... so my first question is: Is there a way to set the data range of an Add-in such as the one below "Chart Hover Label" in VBA?
嗨，我想在 Excel 散点图上的绘制点上添加标签，但是每当我的宏更新时，我的图表数据集范围就会发生变化……所以我的第一个问题是：有没有办法设置 Add 的数据范围- 在诸如 VBA 中“图表悬停标签”下面的那个？
Recording a macro did nothing (my fingers were crossed to begin with).
录制宏什么也没做（一开始我的手指交叉）。
Here is a list of other chart add-ins I know of, from what I know only 1 of these allows you to show ONLY the label when you hover over the plotted point.. I have also not seen one that allows you to show the data range on click of the point.
这是我所知道的其他图表插件的列表，据我所知，其中只有 1 个允许您在将鼠标悬停在绘制的点上时仅显示标签。我也没有看到允许您显示单击该点时的数据范围。
This is the add-in that allows allows you to show only on the hover: http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html
这是允许您仅在悬停时显示的加载项：http: //www.tushar-mehta.com/excel/software/chart_hover_label/index.html
These are the other 2 I know of: http://www.appspro.com/Utilities/ChartLabeler.htmhttp://spreadsheetpage.com/index.php/file/j_walk_chart_tools_add_in/
这些是我知道的另外两个：http: //www.appspro.com/Utilities/ChartLabeler.htmhttp://spreadsheetpage.com/index.php/file/j_walk_chart_tools_add_in/
Does anyone know of any other chart add-ins for Excel (preferably free) that give more options? and can be updated via VBA?
有谁知道 Excel 的任何其他图表加载项（最好是免费的）可以提供更多选项？并且可以通过VBA更新？
Thanks for any help.
谢谢你的帮助。
回答by Rory
I don't know about the add-ins but alot can be done in VBA with chart interactions. Just insert a chart sheet and enter the below code into that sheet in VBA.
我不知道加载项，但可以在 VBA 中通过图表交互完成很多工作。只需插入一个图表表，然后在 VBA 中将以下代码输入到该表中。
Here is an example I have in a working graph of mine. When I click on a series it will create a text box and populate it with text in a cell that is updated in the code below. Its just for the series name, but you can add more functionality to it.
这是我在我的工作图中的一个例子。当我点击一个系列时，它会创建一个文本框，并在下面的代码中更新的单元格中使用文本填充它。它仅用于系列名称，但您可以为其添加更多功能。
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim chart_data As Variant, chart_label As Variant
Dim last_bar As Long, chrt As Chart
Dim ser As Series, Txt As String
On Error Resume Next 'Sorry for this line of code, I haven't had the chance to look into why it was needed.
Me.GetChartElement x, y, ElementID, Arg1, Arg2
Set chrt = ActiveChart
Set ser = ActiveChart.SeriesCollection(1)
chart_data = ser.Values
chart_label = ser.XValues
Set txtbox = ActiveSheet.Shapes("hover") 'I suspect in the error statement is needed for this.
If ElementID = xlSeries Then
txtbox.Delete
Sheet1.Range("Ch_Series").Value = Arg1
Txt = Sheet1.Range("CH_Text").Value
Set txtbox = ActiveSheet.Shapes.AddTextbox _
(msoTextOrientationHorizontal, x - 150, y - 150, 150, 40)
txtbox.Name = "hover"
txtbox.Fill.Solid
txtbox.Fill.ForeColor.SchemeColor = 9
txtbox.Line.DashStyle = msoLineSolid
chrt.Shapes("hover").TextFrame.Characters.Text = Txt
With chrt.Shapes("hover").TextFrame.Characters.Font
.Name = "Arial"
.Size = 12
.ColorIndex = 16
End With
ser.Points(Arg2).Interior.ColorIndex = 44
txtbox.Left = x - 150
txtbox.Top = y - 150
Else
txtbox.Delete
ser.Interior.ColorIndex = 16
End If
End Sub
But you can also do the below for a hover function.
但是您也可以为悬停功能执行以下操作。
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Remember the code needs to be inserted into the Chart sheet and not in a module.
请记住，代码需要插入到图表表中，而不是在模块中。
As for your data range fitting the graph, have you tried dynamic named ranges and then set the graph to reference the named range?
至于拟合图形的数据范围，您是否尝试过动态命名范围，然后将图形设置为引用命名范围？
You could set the MouseMove function to display what you want, then on MouseDown it can navigate to the selected series data range.
您可以设置 MouseMove 功能以显示您想要的内容，然后在 MouseDown 上它可以导航到选定的系列数据范围。
Hope this helps.
希望这可以帮助。
