将悬停标签添加到在Excel 2007中动态更新其数据范围的散点图 [英] Add hover labels to a scatter chart that has it's data range updated dynamically in Excel 2007

问题描述

您好，我想在Excel中的散点图上的绘制点添加标签，但是我的图表数据集范围更改每当我的宏更新...所以我的第一个问题是：
有一种方法设置外接程序的数据范围，如VBA中的图表悬停标签下面的数据范围？

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?

记录一个宏没有作用（我的手指交叉开始）。

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.

这是允许您只显示悬停的加载项：
http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html

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

这些是我知道的其他2：
http://www.appspro.com/Utilities/ChartLabeler.htm
http://spreadsheetpage.com/index.php/file/j_walk_chart_tools_add_in/

These are the other 2 I know of: http://www.appspro.com/Utilities/ChartLabeler.htm http://spreadsheetpage.com/index.php/file/j_walk_chart_tools_add_in/

有没有人知道任何其他图表加载项为Excel（最好是免费），给更多的选择？

Does anyone know of any other chart add-ins for Excel (preferably free) that give more options? and can be updated via VBA?

推荐答案

我不知道加载项，但很多都可以在VBA与图表交互。只需插入一个图表表，并在VBA中输入以下代码到该表。

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.

这是我在我的工作图中的一个例子。当我点击一个系列，它会创建一个文本框，并在下面的代码中更新的单元格中填充文本。它只是为系列名称，但你可以添加更多的功能。

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?

你可以设置MouseMove函数来显示你想要的，然后在MouseDown它可以导航到选择的系列数据范围。

You could set the MouseMove function to display what you want, then on MouseDown it can navigate to the selected series data range.

希望这有助于。

这篇关于将悬停标签添加到在Excel 2007中动态更新其数据范围的散点图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！