Custom message with mouseover

Hello,

I was wondering if anyone has information about a VB script that could display custom information when the mouse is hovered over a bar/column within a pareto chart?

For example, in my example, I show that there are 5 BMWs, 3 Ferraris, and 3 Yugos. If the user put their mouse over one of the bar/columns for BMW, I’d want to display (in a box, as a cloud, etc) the contents of the “Root Data” tab for BMW, which happens to be “BMW-Nice.” Is this possible?

Currently, if the mouse is hovered over the white BMW bar columns, I get the Microsoft output:
Series “White” point “BMW” Value: 3

Thank you!
michael

Yes, this is possible.

If the chart is a separate sheet in the workbook, it is fairly simple. You can use any of the Events (one of which is Mouse Move, so your routine would run every time the mouse is moved).

The Event determines when your routine will run. The next part is knowing where the mouse is pointing. To determine this, use the GetChartElement Method of the Chart object. This Method returns information telling you what part of the chart is being pointed at, and you can decide to ignore move and only respond to some.

I use this quite a bit to allow the user to (for example) click on a point in a scatter chart, then tell them a bunch of stuff I think they might want to know about that point.

If the chart is NOT a separate sheet in the workbook, all of these same principles apply; but, you have to do more work to make it happen. My solution for this is to create a Class Module. Once you get to the Chart (through the ChartObject, through the Worksheet), it is exactly like the Chart was a separate sheet.

If you haven't done anything like this before, I would suggest starting with the separate Chart sheet and being sure you have what you want before attempting the Class Module.

Here is the code to insert on the Chart1 object code window.

Dim keepA As Long, keepB As Long

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim searchModel As Range, searchColor As Range
Dim searchComment As Range, lastRow As Long
Dim ID As Long, a As Long, b As Long
    
    Me.GetChartElement x, y, ID, a, b
    If a = keepA And b = keepB Then Exit Sub
    Select Case ID
        Case 3
            Set ws1 = Sheet2
            myColor = ws1.Cells(4, a + 1)
            myModel = ws1.Cells(b + 4, 1)
                
            Set ws2 = Sheet3
            lastRow = ws2.Cells.SpecialCells(xlLastCell).Row
            
            Set searchModel = Range(ws2.Cells(1, 1), ws2.Cells(lastRow, 1))
            Set searchColor = Range(ws2.Cells(1, 2), ws2.Cells(lastRow, 2))
            Set searchComment = Range(ws2.Cells(1, 5), ws2.Cells(lastRow, 5))

            'MsgBox myColor, vbOKOnly, myModel
            For i = 1 To lastRow
                If searchModel.Cells(i) = myModel Then
                    If searchColor.Cells(i) = myColor Then
                        MsgBox searchComment.Cells(i)
                        Exit For
                    End If
                End If
            Next i
            
            keepA = a
            keepB = b
        Case Else
            'do nothing
    End Select
   
End Sub

keepA and keepB are declared outside of the Event procedure so that you do not get the message box again until you mouse over a different model or different color.

You would want to replace the MsgBox with something more elaborate, I assume.

First off, thank you, thank you, thank you!!! Thank you for the explanation, and thank you for the code. I’m new to VBA, so this is more advanced for me but I have a programming background which will allow me to break this down and understand what you did.

Just out of curiousity, is there a way to display a message that does not use the message box? The reason is that with the message box the user always has to press enter to acknowledge the message. Would it be possible to display a message when the mouse is over a bar/column and then this message disapears when the mouse goes away?

I thought that the status bar might work for this, but after experimenting I think another solution would be better. The examples I found for a message box always seem to require user interaction to close/acknowledge the box.

Thank you again so much!

Yes, definitely. I knew you would not want a message box. You want a floating message that appears and disappears. I'll work on that and post it here.