Error on GetChartElement when clicking on legend entry

[tinyskills13]

I have (found) a code that returns info about a chart on the "MouseUp" event. I edited this code to go to a new chart when the user CTRL + clicks on a pie piece. So far that function is working. The other function I am looking to add is going to a list on another sheet when the user CTRL + clicks on a legend entry. However, when I click on a legend entry the code errors out at the GetChartElement method:

"Run-time error '430':
Class does not support Automation or does not support expected interface."
​

I'm on a Windows Vista machine and using Excel 2007. I read something about certain chart commands not working in 2007, but I am not sure if this falls in that category. It might also be helpful to know that the charts are on separate chart sheets and not normal worksheets. Small portion of code is below. Perhaps I am missing something obvious? Thanks.

Option Explicit
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double, myXlen As Long
 
If Shift = 2 Then
With ActiveChart 
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

 

[tinyskills13]

I did some testing using the simple code below found on the microsoft website. The code works if a bar chart is used on a chart sheet. But I get the same problem as I originally posted when hovering over the legend of a pie chart. Maybe it has something to do with the fact that the legend of a pie chart contains x values instead of series names? I don't know. Any suggestions for a workaround would be greatly appreciated.

Private Sub Chart_MouseMove(ByVal Button As Long, _
 ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
 Dim IDNum As Long
 Dim a As Long
 Dim b As Long
 
 ActiveChart.GetChartElement X, Y, IDNum, a, b
 If IDNum = xlLegendEntry Then _
 MsgBox "WARNING: Move away from the legend"
End Sub

 

[tinyskills13]

Perhaps, I am just talking to myself, but I found a temporary (but messy) workaround. It involves error handling and using the Y value from the mouseUp event to determine where on the screen the user clicks. Unfortunately this entailed making a trendline of y position of top of legend entry versus legend entry index and then using the coefficients (slope and intercept) to get the index.

Good news: my code works.
Bad news: it is far from robust (need new trendline if number of legend entries changes, and it depends on zoom level)

I am open to any other suggestions. Is there another way to get the legend entry index from the position of the click?

 

[sixfootandproud]

Did you instantiate your variables?

 

[tinyskills13]

Did you instantiate your variables?

Yeppers. Did you try running the code I copied from Microsoft in my second post? Even that causes error with a pie chart.

I feel like there HAS to be another way to get the legend entry number. After all, when you hover over an entry the floating message gives the correct number. I just wish I knew how to access that information.