Hover-Over Tooltips on a line Graph

J

Jerry138889

New Member
Joined
May 23, 2013
Messages
28
Hi. I have a line graph which is reasonably linear. However, at some points on the graph, there are significant changes. I would like a user to be able to hover over those points and see an explanatory note. Let's say the graph was sales and point A was where the business had to close because of a large weather event. Then point B was Christmas sales or some such item that led to a rapid increase and say item C was due to a global recession or something. Hovering over A would say "Hurricane Jimmy", hovering over B would say "Christmas rush" and C would say "2008 recession"....something along those lines. I'm not looking to be verbose.

18_imageexcel.png


http://img4.imagetitan.com/img.php?image=18_imageexcel.png
img.php


Is this possible? I have done plenty of googling but cannot find a satisfactory answer and perhaps there is no easy way of doing it. I don't necessarily want to add a note for every point as there are over 1000 data points contributing to the line graph. Finally, the graph will update as will the axes when more data points are added over the next few months, so while in the past I had added a SmartArt object and a note but when the data moves, the object doesn't move with it and is then out of place.

Using Excel 2010.

Any help would be appreciated. It would be best if the solution didn't involve an add in that I can't install on a work PC but perhaps I can find a way around that restriction. Thanks for your help.
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Jerry138889 said:
Hi. I have a line graph which is reasonably linear. However, at some points on the graph, there are significant changes. I would like a user to be able to hover over those points and see an explanatory note. Let's say the graph was sales and point A was where the business had to close because of a large weather event. Then point B was Christmas sales or some such item that led to a rapid increase and say item C was due to a global recession or something. Hovering over A would say "Hurricane Jimmy", hovering over B would say "Christmas rush" and C would say "2008 recession"....something along those lines. I'm not looking to be verbose.

18_imageexcel.png


http://img4.imagetitan.com/img.php?image=18_imageexcel.png
img.php


Is this possible? I have done plenty of googling but cannot find a satisfactory answer and perhaps there is no easy way of doing it. I don't necessarily want to add a note for every point as there are over 1000 data points contributing to the line graph. Finally, the graph will update as will the axes when more data points are added over the next few months, so while in the past I had added a SmartArt object and a note but when the data moves, the object doesn't move with it and is then out of place.

Using Excel 2010.

Any help would be appreciated. It would be best if the solution didn't involve an add in that I can't install on a work PC but perhaps I can find a way around that restriction. Thanks for your help.
Click to expand...

Have you seen this ?
 
Upvote 0
Jaafar Tribak said:
Have you seen this ?
Click to expand...

Hi Jafaar

This is actually pretty good. I've been looking at it for the past while but I am struggling to grasp how it works, however. My understanding of macros is very limited. I don't understand how the macro looks at the data and assigns the labels. I don't see any references within the macro he has written that specify the cells from which to grab the label/hover over info. Is it the "???.??" part?

I essentially have this data and want to show it. Not every data point has a label - only extreme values:

DateSalesHover Label
07/06/2018

<tbody>
</tbody>
1000
08/06/2018

<tbody>
</tbody>
1100
09/06/2018

<tbody>
</tbody>
1200
10/06/2018

<tbody>
</tbody>
1300
11/06/2018

<tbody>
</tbody>
500Weather Event
12/06/2018

<tbody>
</tbody>
1200
13/06/2018

<tbody>
</tbody>
1250
14/06/2018

<tbody>
</tbody>
3000Christmas Sales
15/06/2018

<tbody>
</tbody>
1500

<tbody>
</tbody>


Really appreciate your help.
 
Upvote 0
Jerry138889 said:
It's embedded but if it makes a huge difference then I can put it on a chart sheet
Click to expand...

Hi Jerry,

This is for an enbedded chart .. I have used a different API_based approach that transforms a native MS UserForm to an actual tooltip.

Perhaps, the best dvantage of this approach over the codes that I have seen all over the internet is that it doesn't need to activate the chart beforehand which makes the process flicker-free plus it doesn't require to add additional shapes to the worksheet.

The AddToolTipToChartPoint SUB, lets you flexibly choose the attributes of each individual tootip such as: text,width,height,font,color etc...

Workbook example:






1- Add a new blank userform (empty- No controls) to your vba project , name the userform ChartToolTipsCollection and place this code in its module:
Code: 
Option Explicit

Private WithEvents ch As Chart
Private WithEvents cmbrs As CommandBars

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
        Private Declare PtrSafe Function GetClassLong Lib "user32" Alias "GetClassLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetClassLong Lib "user32" Alias "SetClassLongPtrA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As Long) As LongPtr
        Private Declare PtrSafe Function GetClassLong Lib "user32" Alias "GetClassLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr
        Private Declare PtrSafe Function SetClassLong Lib "user32" Alias "SetClassLongA" (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hdc As LongPtr) As Long
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function MoveWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
    Private Declare PtrSafe Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As LongPtr) As Long
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)

    Private hwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function GetClassLong Lib "user32" Alias "GetClassLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function SetClassLong Lib "user32" Alias "SetClassLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
    Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function MoveWindow Lib "user32" (ByVal hwnd As Long, ByVal x As Long, ByVal y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Long) As Long
    Private Declare Function WindowFromAccessibleObject Lib "oleacc" (ByVal pacc As IAccessible, phwnd As Long) As Long
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

    Private hwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90
Private Const PointsPerInch = 72
Private Const GWL_STYLE = (-16)
Private Const GCL_STYLE = -26
Private Const GWL_EXSTYLE = (-20)
Private Const WS_CAPTION = &HC00000
Private Const WS_THICKFRAME = &H40000
Private Const WS_EX_DLGMODALFRAME = &H1&
Private Const CS_DROPSHADOW = &H20000

Private bTipNames As Boolean, bTipValues As Boolean

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    WindowFromAccessibleObject Me, hwnd
    SetWindowLong hwnd, GWL_STYLE, (GetWindowLong(hwnd, GWL_STYLE) And Not WS_CAPTION)
    SetWindowLong hwnd, GWL_EXSTYLE, (GetWindowLong(hwnd, GWL_EXSTYLE) And Not WS_EX_DLGMODALFRAME)
    MoveWindow hwnd, -20, -20, 10, 10, True
    With Application
        bTipNames = .ShowChartTipNames
        bTipValues = .ShowChartTipNames  '
        .ShowChartTipNames = False
        .ShowChartTipValues = False
    End With
End Sub

Private Sub UserForm_Activate()
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub UserForm_Terminate()
    Application.ShowChartTipNames = bTipNames
    Application.ShowChartTipValues = bTipValues
End Sub


Private Sub cmbrs_OnUpdate()
    Dim tSeriesPointXY As POINTAPI
    Dim tCursorPos As POINTAPI
    Dim oCol As New Collection
    Dim ar() As String
    Dim arTemp() As String
    Dim oLbl As Control
    Dim i As Long
    Dim sToolTipAttributes As String
    
    If Not ActiveWorkbook Is ThisWorkbook Then Exit Sub

    With Application.CommandBars.FindControl(ID:=2040)
        .Enabled = Not .Enabled
    End With

    CopyMemory oCol, CLngPtr(Me.Tag), 8
    GetCursorPos tCursorPos
    
    For i = 1 To oCol.Count
        sToolTipAttributes = oCol.Item(i)
        ar = Split(sToolTipAttributes, "*")
        With Sheets(ar(10)).ChartObjects(ar(1))
            tSeriesPointXY.x = PTtoPX((.Left + .Chart.SeriesCollection(1).POINTS(ar(0)).Left) * ActiveWindow.Zoom / 100, False) + ActiveWindow.PointsToScreenPixelsX(0)
            tSeriesPointXY.y = PTtoPX((.Top + .Chart.SeriesCollection(1).POINTS(ar(0)).Top) * ActiveWindow.Zoom / 100, True) + ActiveWindow.PointsToScreenPixelsY(0)
        End With
        If Abs(tCursorPos.x - tSeriesPointXY.x) <= 12 And Abs(tCursorPos.y - tSeriesPointXY.y) <= 12 Then
            ReDim arTemp(8)
            arTemp(0) = ar(2)
            arTemp(1) = ar(3)
            arTemp(2) = ar(4)
            arTemp(3) = ar(5)
            arTemp(4) = ar(6)
            arTemp(5) = ar(7)
            arTemp(6) = ar(8)
            arTemp(7) = ar(9)
            GoTo XitFor
        End If
    Next
XitFor:
    On Error GoTo xit
    If TypeName(ActiveWindow.RangeFromPoint(tCursorPos.x, tCursorPos.y)) = "ChartObject" Then
        If Abs(tCursorPos.x - tSeriesPointXY.x) <= 12 And Abs(tCursorPos.y - tSeriesPointXY.y) <= 12 Then
        SetClassLong hwnd, GCL_STYLE, GetClassLong(hwnd, GCL_STYLE) Or CS_DROPSHADOW
        If Not oLbl Is Nothing Then oLbl.Delete
        Set oLbl = Me.Controls.Add("Forms.Label.1", "Test", True)
        With oLbl
            .Caption = arTemp(2): .Left = 0:  .Width = arTemp(0): .Height = arTemp(1): .Top = 0
            .BackColor = arTemp(7): .Font.Bold = CBool(arTemp(5)): .ForeColor = arTemp(6)
            .TextAlign = 2: .Font.Size = arTemp(4):: .Font.Name = arTemp(3) ': .WordWrap = True
        End With
            Me.Show
            MoveWindow hwnd, tSeriesPointXY.x, tSeriesPointXY.y - 10 - PTtoPX(oLbl.Height, True), PTtoPX(oLbl.Width, False), PTtoPX(oLbl.Height, True), True
        Else
            SetClassLong hwnd, GCL_STYLE, GetClassLong(hwnd, GCL_STYLE) And Not CS_DROPSHADOW
            Me.Hide
        End If
    Else
        SetClassLong hwnd, GCL_STYLE, GetClassLong(hwnd, GCL_STYLE) And Not CS_DROPSHADOW
        Me.Hide
    End If
xit:
    CopyMemory oCol, 0, 8
End Sub

Private Function PTtoPX(POINTS As Single, bVert As Boolean) As Long
    PTtoPX = POINTS * ScreenDPI(bVert) / PointsPerInch
End Function

Private Function ScreenDPI(bVert As Boolean) As Long
   Static lDPI(1), lDC
   If lDPI(0) = 0 Then
        lDC = GetDC(0)
        lDPI(0) = GetDeviceCaps(lDC, LOGPIXELSX)
        lDPI(1) = GetDeviceCaps(lDC, LOGPIXELSY)
        lDC = ReleaseDC(0, lDC)
    End If
    ScreenDPI = lDPI(Abs(bVert))
End Function

2- Code Usage Example ( In a Standard Module) .

Run the LoadToolTips Macro to start and when done, run the UnLoadToolTips Macro... You could run those in the Workbook open and Beforeclose events if you want)
Code: 
Option Explicit

Dim oCol As Collection
Dim ChartToolTips As ChartToolTipsCollection

Public Sub LoadToolTips()

    [B][COLOR=#008000]' Tooltip for chart point(5).[/COLOR][/B]
    [B][COLOR=#008000]'===========================[/COLOR][/B]
    Call AddToolTipToChartPoint( _
        Chart:=Sheet1.ChartObjects(1), _
        Point:=5, _
        ToolTipWidth:=100, _
        ToolTipHeight:=40, _
        ToolTipText:=Sheet1.Range("C7").Value, _
        ToolTipFontName:="Ravie", _
        ToolTipFontSize:=12, _
        ToolTipFontBold:=False, _
        ToolTipFontColor:=vbWhite, _
        ToolTipColor:=vbBlack _
    )
    
    [B][COLOR=#008000]' Tooltip for chart point(8).[/COLOR][/B]
   [B][COLOR=#008000] '===========================[/COLOR][/B]
    Call AddToolTipToChartPoint( _
        Chart:=Sheet1.ChartObjects(1), _
        Point:=8, _
        ToolTipWidth:=100, _
        ToolTipHeight:=60, _
        ToolTipText:=" " & vbCrLf & Sheet1.Range("C10").Value, _
        ToolTipFontName:="Arial", _
        ToolTipFontSize:=14, _
        ToolTipFontBold:=True, _
        ToolTipFontColor:=vbRed, _
        ToolTipColor:=&H80FFFF _
    )
    
    [B][COLOR=#008000]'load the tooltips.[/COLOR][/B]
    ChartToolTips.Show vbModeless

End Sub

Public Sub UnloadToolTips()
    If Not oCol Is Nothing Then UnLoad ChartToolTips: Set oCol = Nothing
End Sub

Public Sub AddToolTipToChartPoint( _
    ByVal Chart As ChartObject, _
    ByVal Point As Long, _
    Optional ByVal ToolTipWidth As Long, _
    Optional ByVal ToolTipHeight As Long, _
    Optional ByVal ToolTipText As String, _
    Optional ByVal ToolTipFontName As String, _
    Optional ByVal ToolTipFontSize As Long, _
    Optional ByVal ToolTipFontBold As Boolean, _
    Optional ByVal ToolTipFontColor As Variant, _
    Optional ByVal ToolTipColor As Variant)
    
        ToolTipWidth = IIf(ToolTipWidth = 0, 100, ToolTipWidth)
        ToolTipHeight = IIf(ToolTipHeight = 0, 40, ToolTipHeight)
        ToolTipFontName = IIf(ToolTipFontName = "", "Calibri", ToolTipFontName)
        ToolTipFontSize = IIf(ToolTipFontSize = 0, 12, ToolTipFontSize)
        ToolTipFontBold = IIf(ToolTipFontBold = False, False, ToolTipFontBold)
        If IsMissing(ToolTipFontColor) Then ToolTipFontColor = 0
        If IsMissing(ToolTipColor) Then ToolTipColor = &H80FFFF
        
        If oCol Is Nothing Then Set oCol = New Collection: Set ChartToolTips = New ChartToolTipsCollection
        
        oCol.Add Point & "*" & Chart.Name & "*" & ToolTipWidth & "*" & ToolTipHeight & "*" & _
        ToolTipText & "*" & ToolTipFontName & "*" & ToolTipFontSize & "*" & _
        ToolTipFontBold & "*" & ToolTipFontColor & "*" & ToolTipColor & "*" & Chart.Parent.Name, CStr(oCol.Count + 1)
        ChartToolTips.Tag = CStr(ObjPtr(oCol))
End Sub

Note:
The code only works for one data serie chart .
 
Upvote 0
Jaafar Tribak said:
Hi Jerry,

This is for an embedded chart .. .
Click to expand...

Jafaar, this is incredible stuff. It's amazing how complicated this has to be though! Wish it was more straightforward. It looks like I'd need to add a section into the tooltip every time I add in a note and while that won't be extremely often, it is not as easy as I'd hoped. Still though, it's really great work and I now understand enough of it to apply it to my workbook. I think haha.
 
Upvote 0
Jerry138889 said:
Jafaar, this is incredible stuff. It's amazing how complicated this has to be though! Wish it was more straightforward. It looks like I'd need to add a section into the tooltip every time I add in a note and while that won't be extremely often, it is not as easy as I'd hoped. Still though, it's really great work and I now understand enough of it to apply it to my workbook. I think haha.
Click to expand...

Thanks for the feedback Jerry and glad it worked for you.

Yes, the bulk of the code is not straight-forward however, it is encapsulated and insulated inside the userform module and the user doesn't have to worry about it or alter it in any way.

If you wanted to add a new tooltip to another point in the chart series you just call the AddToolTipToChartPoint SUB again from outside the userform's module and pass the new tooltip attributes in the SUB's arguments ... It is quiet flexible for the user.

something like this :

Code: 
Public Sub LoadToolTips()

[COLOR=#008000]    ' Tooltip for chart point(5).[/COLOR]
[COLOR=#008000]    '===========================[/COLOR]
    Call AddToolTipToChartPoint( _
        Chart:=Sheet1.ChartObjects(1), _
        Point:=5, _
        ToolTipWidth:=100, _
        ToolTipHeight:=40, _
        ToolTipText:=Sheet1.Range("C7").Value, _
        ToolTipFontName:="Ravie", _
        ToolTipFontSize:=12, _
        ToolTipFontBold:=False, _
        ToolTipFontColor:=vbWhite, _
        ToolTipColor:=vbBlack _
    )
    
[COLOR=#008000]    ' Tooltip for chart point(8).[/COLOR]
[COLOR=#008000]    '===========================[/COLOR]
    Call AddToolTipToChartPoint( _
        Chart:=Sheet1.ChartObjects(1), _
        Point:=8, _
        ToolTipWidth:=100, _
        ToolTipHeight:=60, _
        ToolTipText:=" " & vbCrLf & Sheet1.Range("C10").Value, _
        ToolTipFontName:="Arial", _
        ToolTipFontSize:=14, _
        ToolTipFontBold:=True, _
        ToolTipFontColor:=vbRed, _
        ToolTipColor:=&H80FFFF _
    )
    
    
[B][COLOR=#0000ff]    ' HERE, YOU ADD TOOLTIPS FOR ANY SUBSEQUENT CHART POINTS
[/COLOR][/B][B][COLOR=#0000ff]   '========================================================[/COLOR][COLOR=#0000ff]
[/COLOR][/B]
[B][COLOR=#0000ff]     '   AddToolTipToChartPoint(Chart, Point[/COLOR][COLOR=#ff0000]X[/COLOR][COLOR=#0000ff], etc -----[/COLOR][/B]
[B][COLOR=#0000ff]     '   AddToolTipToChartPoint(Chart, Point[/COLOR][COLOR=#ff0000]Y[/COLOR][COLOR=#0000ff], etc -----[/COLOR][/B]
[B][COLOR=#0000ff]     '   AddToolTipToChartPoint(Chart, Point[/COLOR][COLOR=#ff0000]Z[/COLOR][COLOR=#0000ff], etc -----[/COLOR][/B]
    
        
[COLOR=#008000]    'load the tooltips.[/COLOR]
    ChartToolTips.Show vbModeless


End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Excel coordinate pointer on line graph or curve.
Replies
0
Views
719
dmalik
D
U
  • Question
Line graph from data in multiple worksheets
Replies
2
Views
2K
ask2tsp
ask2tsp
C
  • Question
Problem when exporting line graph the points are not equally spaced out
Replies
0
Views
514
chompyrud
C
sijpie
  • Question
Short Guide to Better VBA
2
Replies
13
Views
21K
skorpionkz
skorpionkz
F
  • Question
How do I add a straight horozontal line in Column Chart?
Replies
3
Views
1K
Financeer
F

Forum statistics

Threads
1,215,419
Messages
6,124,796
Members
449,189
Latest member
kristinh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top