Custom message with mouseover

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.

Hi Bryan,

Haven't looked at the attachment yet. I am sure it is better than what I came up with.

Meanwhile, Michael, here is what I came up with ... replace the code above with the code below (on the Pareto chart's code window). To use this, you need to have first created a shape (I chose a rectangle, but you can make it any auto shape you like) and Name it "CommentBox" (easiest way to do this is to use the Name box that is to the left of the Excel formula bar ... always remember to press Enter after typing in a name or the name will not "stick").

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
Dim s As Series, p As Point
Dim sh As Shape, strComment As String
    
    On Error GoTo leave
    Me.GetChartElement x, y, ID, a, b
    Set sh = Me.Shapes("CommentBox")
    Select Case True
        Case a = keepA And b = keepB
            'sh.Visible = False
        Case ID = 3
            Set ws1 = Sheet2
            myColor = ws1.Cells(4, a + 1)
            myModel = ws1.Cells(b + 4, 1)
            
            'do not need these, but this is what a & b represent:
            Set s = Me.SeriesCollection(a)
            Set p = s.Points(b)
'            MsgBox WorksheetFunction.Index _
'                    (Me.SeriesCollection(a).XValues, b)
'            MsgBox WorksheetFunction.Index _
'                    (Me.SeriesCollection(a).Values, b)
                
            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
                        strComment = vbLf & s.Name & vbLf
                        strComment = strComment & searchComment.Cells(i) & vbLf
                        With sh
                            .TextFrame.Characters.Text = strComment
                            .TextFrame.AutoSize = True
                            .Visible = True
                            'need to convert pixels to twips
                            'to get the Left & Top exactly right
                            .Left = x * 2 / 3
                            .Top = y * 2 / 3
                        End With
                        Exit For
                    End If
                End If
            Next i
            'remember these so we do not keep doing the same thing
            'over and over again
            keepA = a
            keepB = b
        Case Else
            sh.Visible = False
            keepA = 0
            keepB = 0
    End Select

leave:
End Sub

Bryan, that is a great form you created! I am sure I will find it useful. I will be sure to credit you in any code I use it with.

Michael, take at look at Bryan's workbook. It might appear too "formal" for the impact you are trying to achieve. (Although, for MY purposes, it looks much more professional than an autoshape!)

If you prefer the professional look and want me to put it into your workbook, let me know. Shouldn't be a problem.

Originally Posted by MSP77079

Hi Bryan,

Haven't looked at the attachment yet. I am sure it is better than what I came up with.

Meanwhile, Michael, here is what I came up with ... replace the code above with the code below (on the Pareto chart's code window). To use this, you need to have first created a shape (I chose a rectangle, but you can make it any auto shape you like) and Name it "CommentBox" (easiest way to do this is to use the Name box that is to the left of the Excel formula bar ... always remember to press Enter after typing in a name or the name will not "stick").

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
Dim s As Series, p As Point
Dim sh As Shape, strComment As String
    
    On Error GoTo leave
    Me.GetChartElement x, y, ID, a, b
    Set sh = Me.Shapes("CommentBox")
    Select Case True
        Case a = keepA And b = keepB
            'sh.Visible = False
        Case ID = 3
            Set ws1 = Sheet2
            myColor = ws1.Cells(4, a + 1)
            myModel = ws1.Cells(b + 4, 1)
            
            'do not need these, but this is what a & b represent:
            Set s = Me.SeriesCollection(a)
            Set p = s.Points(b)
'            MsgBox WorksheetFunction.Index _
'                    (Me.SeriesCollection(a).XValues, b)
'            MsgBox WorksheetFunction.Index _
'                    (Me.SeriesCollection(a).Values, b)
                
            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
                        strComment = vbLf & s.Name & vbLf
                        strComment = strComment & searchComment.Cells(i) & vbLf
                        With sh
                            .TextFrame.Characters.Text = strComment
                            .TextFrame.AutoSize = True
                            .Visible = True
                            'need to convert pixels to twips
                            'to get the Left & Top exactly right
                            .Left = x * 2 / 3
                            .Top = y * 2 / 3
                        End With
                        Exit For
                    End If
                End If
            Next i
            'remember these so we do not keep doing the same thing
            'over and over again
            keepA = a
            keepB = b
        Case Else
            sh.Visible = False
            keepA = 0
            keepB = 0
    End Select

leave:
End Sub

I know I am reviving a pretty old thread here, but this is exactly what I am trying to do except that I have my charts in a worksheet not in an individual "Chart" tab.

The above code works perfectly for me when the chart is in its individual tab, but I have my charts in a worksheet and I am not sure how to get started with this? Any help would be greatly appreciated guys.

(If it would be better for me to start a new thread instead of reviving an old identical one, let me know please and I can start a new thread)