yurble_vn
02-17-2008, 10:45 AM
Dear All,
I have followed Jon Peltier instruction (followed links) and use the Tushar-mehta built code to trap embedded chart event.
http://www.computorcompanion.com/LPMArticle.asp?ID=221
http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html
And it is totally works.
But when I try to get some data in the active sheet from by Cells() function, it always return error:
Run-time error: '1004'
"Methos 'Cells' of Object '_Global' failed"
Is there anyway to get data in currentsheet when in Class?
Please help
Thanks
Below is the code for the Class that I'm trying edit from Tushar-mehta code:
' WhichPointSelected.xls
' Class Module to Enable Events for Embedded Chart
' When a chart is clicked on:
' If a point is under the cursor, the message box shows point information
' If anything else is under the cursor, the message box tells which ElementID it was
' But
' MouseUp doesn't always seem to work:
' It won't capture shapes
' It won't capture chart area of chart embedded in chart sheet
' MouseDown captures shapes, but
' If shape is regular drawing object, mouseup doesn't occur, so object is dragged
' If shape is embedded chart, two message boxes pop up
' ElementID 14 for shape, plus pop up for embedded chart
Option Explicit
' Declare object of type "Chart" with events
Public WithEvents EmbChart As Chart
' Event procedures for "Chart" object go here
' ++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub EmbChart_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 Double, myY As Double
Dim SeriesNameNo As Integer
Dim Sheetname As String
Dim SetCol(1 To 10) As Integer
Dim icount As Integer
Const DataLabelLine As Integer = 28
Sheetname = ActiveSheet.Name
MsgBox Sheetname
For icount = 1 To 10
SetCol(icount) = Cells(icount + 5, "AB") ' <--------------Error Here
Next
If Button = xlPrimaryButton Then
With EmbChart
.GetChartElement X, Y, ElementID, Arg1, Arg2
Application.StatusBar = "[" & ElementID & "]"
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
Application.StatusBar = "[" & myX & ", " & myY & "]"
SeriesNameNo = VBA.Right(.SeriesCollection(Arg1).Name, 1)
MsgBox "Type " & Arg1 & vbCrLf _
& Arg2 & vbCrLf _
& "X = " & myX
End If
End If
End With
End If
Application.StatusBar = False
End Sub
I have followed Jon Peltier instruction (followed links) and use the Tushar-mehta built code to trap embedded chart event.
http://www.computorcompanion.com/LPMArticle.asp?ID=221
http://www.tushar-mehta.com/excel/software/chart_hover_label/index.html
And it is totally works.
But when I try to get some data in the active sheet from by Cells() function, it always return error:
Run-time error: '1004'
"Methos 'Cells' of Object '_Global' failed"
Is there anyway to get data in currentsheet when in Class?
Please help
Thanks
Below is the code for the Class that I'm trying edit from Tushar-mehta code:
' WhichPointSelected.xls
' Class Module to Enable Events for Embedded Chart
' When a chart is clicked on:
' If a point is under the cursor, the message box shows point information
' If anything else is under the cursor, the message box tells which ElementID it was
' But
' MouseUp doesn't always seem to work:
' It won't capture shapes
' It won't capture chart area of chart embedded in chart sheet
' MouseDown captures shapes, but
' If shape is regular drawing object, mouseup doesn't occur, so object is dragged
' If shape is embedded chart, two message boxes pop up
' ElementID 14 for shape, plus pop up for embedded chart
Option Explicit
' Declare object of type "Chart" with events
Public WithEvents EmbChart As Chart
' Event procedures for "Chart" object go here
' ++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub EmbChart_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 Double, myY As Double
Dim SeriesNameNo As Integer
Dim Sheetname As String
Dim SetCol(1 To 10) As Integer
Dim icount As Integer
Const DataLabelLine As Integer = 28
Sheetname = ActiveSheet.Name
MsgBox Sheetname
For icount = 1 To 10
SetCol(icount) = Cells(icount + 5, "AB") ' <--------------Error Here
Next
If Button = xlPrimaryButton Then
With EmbChart
.GetChartElement X, Y, ElementID, Arg1, Arg2
Application.StatusBar = "[" & ElementID & "]"
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
Application.StatusBar = "[" & myX & ", " & myY & "]"
SeriesNameNo = VBA.Right(.SeriesCollection(Arg1).Name, 1)
MsgBox "Type " & Arg1 & vbCrLf _
& Arg2 & vbCrLf _
& "X = " & myX
End If
End If
End With
End If
Application.StatusBar = False
End Sub