Displaying text with a Mousover event for an embedded chart

  • Hello. I am an excel newbie. I am using John Walkenback’s book called “Excel Charts” to include mouseover events for an embedded chart. I am having a slight issue with the code in my workbook. When the mouse pointer is positioned over a column, a textbox in the upper left of the chart should appear as a “custom tool tip” from data in a range on the spreadsheet. I copied the code from the companion CD with the book and modified it accordingly. My workbook consists of several worksheets. Each sheet has at least 5 embedded charts. My VBA is not reading the range of the cell comments. No results are returned or displayed. I modified the code from the book to suite my needs. Below is the code for the module.

    Here is the code for the Class Module.

    I think my issue is with the line in bold. I graciously appreciate any assistance any of you can provide. If I posted or referenced my sources incorrectly, I apologize in advance. Workbook is attached.

    Walkenback, John.Excel Charts. Indiana: Wiley, 2003

  • Re: Displaying text with a Mousover event for an embedded chart

    It works. Thank you so much Junho! You are truly awesome.

    Here is the modified code for other interested parties. Insert a rectangle into your chart and add the code below.

    Module Code
    Dim myClassModule As New Class1

    Sub ConnectChart()
    Set myClassModule.MyChart = ActiveChart
    End Sub

    Sub DisconnectChart()
    Set myClassModule = Nothing
    End Sub


    Class Code
    Option Explicit
    Public WithEvents MyChart As Chart

    Private Sub MyChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _
    ByVal x As Long, ByVal y As Long)
    Dim ElementId As Long
    Dim arg1 As Long, arg2 As Long
    Dim NewText As String
    On Error Resume Next
    MyChart.GetChartElement x, y, ElementId, arg1, arg2
    If ElementId = xlSeries Then
    Select Case MyChart.Name
    Case "Cars Pchart"
    NewText = Sheets("Cars").Range("Comments").Offset(arg2, arg1)
    'Case "Cars Chart 7"
    ' NewText = Sheets("Cars").Range(MyChart.SeriesCollection(arg1).Name).Cells(arg2)
    'Case "Cars Chart 8"
    ' NewText = Sheets("Cars").Range(MyChart.SeriesCollection(arg1).Name).Cells(arg2)
    'Case "Cars Chart 9"
    ' NewText = Sheets("Cars").Range(MyChart.SeriesCollection(arg1).Name).Cells(arg2)
    Case Else
    NewText = Sheets("Cars").Range(MyChart.SeriesCollection(arg1).Name).Cells(arg2)
    End Select
    NewText = ""
    ActiveChart.Shapes(1).Visible = False
    End If
    If NewText <> MyChart.Shapes(1).TextFrame.Characters.Text Then
    ActiveChart.Shapes(1).TextFrame.Characters.Text = NewText
    ActiveChart.Shapes(1).Visible = True
    End If
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!