VBA Find Method Deselects Charts

[JeffK627]

I'm using the following code in conjunction with an embedded chart to find the row where a specified value is located and use that value in a custom tooltip. The problem is that when this runs it deselects the chart and goes back to the last selected cell, and you have to click on the chart again to continue, which I don't want. Does anyone know a way to do this without using Find, or how to prevent Find from deselecting the chart? I'm working in Excel 2010.

Function FindTheRow(Rng As Range, sToFind As String) As Long
    FindTheRow = Rng.Cells.Find(What:=sToFind, _
                                After:=Rng.Cells(1), _
                                lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
End Function

 

[pgc01]

Hi

I tried and it did not deselect the chart.

Also I did not understand the "you have to click on the chart again to continue".
You don't usually select objects in vba.

 

[JeffK627]

I'm using the mouse move event of the chart to display a custom tooltip when the mouse is over a data point. It only works if the chart is selected. Note that this is a chart embedded in a worksheet, not a chart sheet. I was able to get around it, the solution is too long to post here though. Thanks.