PDA

View Full Version : Custom Messaging with Mouseover



sam314159
01-26-2010, 03:04 PM
I have a worksheet that contains 5-6 bar charts on it and I am trying to display additional data to the user whenever the mouse hovers over one of the bars.

I found the code below at:

http://www.excelforum.com/excel-programming/583364-custom-message-with-mouseover.html

And it works perfectly whenever the chart is in its seperate 'Chart' tab, but I can't figure out how to make it work when the charts are in a worksheet instead. Any ideas guys? Thanks in advance for the help.


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