Add hover labels to a scatter chart that has it's data range updated dynamically in Excel 2007

Question

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.

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

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/

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

Thanks for any help.

Answer 1

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?

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

Hope this helps.