Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2010 - Can't assign macro to part of a Column Chart (Data Point) 1

Status
Not open for further replies.
PWD

PWD

Technical User
Jul 12, 2002
823
GB
Good morning, I was hoping that I could assign a macro to a Data Point in a Column Chart so that the user could then see additional narrative that goes with the figures - just something simple like selecting another worksheet actually; unfortunately I only get the Series context shortcuts when I right click. Am I asking too much?

I have been pointed towards 'GetChartElement' by Skip but think I may have lost my way. I'm trying to use point index to determine the co-ordinates of the object I really want to act as a macro button but can't find a definitive list of their meanings only,

"point_index depends on the item we're looking at, but is a number from 1 to 8 to identify a specific vertex within the item. For example, 2 is the upper middle of any rectangular item, such as a column in a column chart."
Click to expand...

Which is all very well and good but I can't be sure I'm using the right ones.

Anyway, I've found some more code that gets me "some" x & y co-ordinates for my object but when I proceed further I get,

Run-time error '438':
Object doesn't support this property or method
Click to expand...

Code: 
Sub test2()

  Dim dXVal As Double
  Dim dYVal As Double

  'We have to activate the chart to use GET.CHART.ITEM
  ActiveSheet.ChartObjects("Chart 14").Activate

  dXVal = ExecuteExcel4Macro("GET.CHART.ITEM(1,1,""S2P6"")") '= 425.25
  dYVal = ExecuteExcel4Macro("GET.CHART.ITEM(2,4,""S2P6"")") '= 124.5

ActiveSheet.ChartObjects("Chart 14").SeriesCollection(2).Points(6).GetChartElement dXVal, dYVal, xlNothing

End Sub

And even if it didn't error, I'm not really sure how far this would get me!!

Thoughts gratefully received.

Many thanks,
D€$
 
Sort by date Sort by votes
GetChartElement is used in a Chart Event, like the MouseDown event. Yes, it detects the coordinates of the mouse, but according to the Chart Element of interest, like the Series Element for instance, it returns the Series number in Arg1 and the Point number in Arg2, which are the values that you probably need to know.

So to review, first you must expose the Chart Events for your embedded chart, and then you'll be able to use the GetChartElement to detect stuff clicked on in the chart.
 
Upvote 0 Downvote
Hi Skip, this is hurting my brain.

Yes, (I think!) I know (for this test) that I'm going for Point 6 in Series 2. But I'm really not sure what to do once I've established its x & y co-ordinates.

I had also wondered about about trying to trigger a "Worksheet_SelectionChange" in the sheet but that only really seems applicable to cell selection.

How do I "expose the Chart Events for your embedded chart"

Sorry to be thick.

Many thanks,
D€$
 
Upvote 0 Downvote
You do NOT use x-y coordinates!

You use the two arg arguments to get Arg1 returns 2 & Arg2 returns 6. And you get that within a chart event using the GetChartElement method.

Expose embedded chart events: check out the first link I posted in your thread in forum68.

Of course, if you put your chart in a Chart Sheet, the events are exposed be default!
 
Upvote 0 Downvote
Hi Skip, I have moved the chart to its own sheet and I've managed to cobble something together from code "out there":

Code: 
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As Variant
Dim chrt As Chart
Dim ser As Series

'On Error Resume Next

Me.GetChartElement x, y, ElementID, Arg1, Arg2
'
Application.ScreenUpdating = False
Set chrt = ActiveChart
Set ser = ActiveChart.SeriesCollection(2)
chart_data = ser.Values
chart_label = ser.XValues

If ElementID = xlSeries Then

    If Arg1 = "2" And Arg2 <> "1" Then 'Series 2 but not Summary
    
        ' Promt
        strPrompt = "Do you want to review the data?"
     
        ' Dialog's Title
        strTitle = "Review Data for: " & chart_label(Arg2)
     
        'Display MessageBox
        iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)
     
        ' Check pressed button
        If iRet = vbYes Then
        Worksheets(chart_label(Arg2)).Activate
        Application.ScreenUpdating = True
        Exit Sub
        End If
    
    End If

Else

End If 'If ElementID = xlSeries Then

Application.ScreenUpdating = True
End Sub

Trouble is, it crashes Excel every time it activates the relevant worksheet. For a while it did what I wanted but appeared to freeze the activated worksheet.

I know I've probably done this incorrectly but this is my first experience of doing this specific bit of programing and it's all a bit baffling.

Many thanks,
D€$
 
Upvote 0 Downvote
What does Chart_label(Arg2) contain? You can use the Watch Window to discover things like this.

faq707-4594
 
Upvote 0 Downvote
What does Chart_label(Arg2) contain? You can use the Watch Window to discover things like this.

faq707-4594
 
Upvote 0 Downvote
Oh that's the data point text, each one of which is identical to the worksheet names.

Many thanks,
D€$
 
Upvote 0 Downvote
I'm fishing here.

I'd strongly suggest two practices in general:

1) always set the object variables to NOTHING before you exit the procedure.

2) nearly always avoid a premature exit. In your procedure just remove Exit Sub. That way all the cleanup will execute.

Hear are several others...
Code: 
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As Variant
Dim chrt As Chart
Dim ser As Series

'On Error Resume Next
Set chrt = ActiveChart
Chrt.GetChartElement x, y, ElementID, Arg1, Arg2
'
Application.ScreenUpdating = False

Set ser = Chrt.SeriesCollection(2)
chart_data = ser.Values
chart_label = ser.XValues

   Select Case ElementID 
       Case xlSeries
          If Arg1 = "2" And Arg2 <> "1" Then 'Series 2 but not Summary
    
        ' Promt
          strPrompt = "Do you want to review the data?"
     
        ' Dialog's Title
          strTitle = "Review Data for: " & chart_label(Arg2)
     
        'Display MessageBox
          iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)
     
        ' Check pressed button
          If iRet = vbYes Then
             Worksheets(chart_label(Arg2)).Activate
          End If
    
   End Select

   Set chrt = Nothing
   Set ser = Nothing
   Application.ScreenUpdating = True
End Sub
 
Upvote 0 Downvote
Hi Skip, thanks for this. It still errors - but right at the "End Sub". Weird. Weird. Frustrating!!!

Many thanks,
D€$
 
Upvote 0 Downvote
Something does not make nay sense.

How can a data point on a chart, indexed by Arg2, be a worksheet name (which are text values) when chart data points ought to be numeric values???
 
Upvote 0 Downvote
Did you actually run the code and BREAK to view this variable value in the Watch Window or in the Immediate Window?
 
Upvote 0 Downvote
Hi Skip, I'm referencing the text of the x axis - what I'm calling the "data point text". It kept on crashing so I put in a break and then stepped through with the F8 key. It does activate the correct worksheet but just crashes.

This is the actual code I'm using:

Code: 
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As Variant
Dim chrt As Chart
Dim ser As Series

'On Error Resume Next
Set chrt = ActiveChart
chrt.GetChartElement x, y, ElementID, Arg1, Arg2
'
'Application.ScreenUpdating = False

Set ser = chrt.SeriesCollection(2)
chart_data = ser.Values
chart_label = ser.XValues

   Select Case ElementID
       Case xlSeries
          If Arg1 = "2" And Arg2 <> "1" Then 'Series 2 but not Summary
    
        ' Promt
          strPrompt = "Do you want to review the data?"
     
        ' Dialog's Title
          strTitle = "Review Data for: " & chart_label(Arg2)
     
        'Display MessageBox
          iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)
     
            ' Check pressed button
              If iRet = vbYes Then
                 Worksheets(chart_label(Arg2)).Activate
              End If
              
           End If

   End Select

   Set chrt = Nothing
   Set ser = Nothing
  ' Application.ScreenUpdating = True
End Sub

So near, yet so....... frustrating.

Many thanks,
D€$
 
Upvote 0 Downvote
Hi Skip, tried this last night but it ended with another Excel crash.

Many thanks,
D€$
 
Upvote 0 Downvote
Anyway, thanks for all your help but I guess I'll just have to forget doing this, which is a shame. There's nothing else apart from MouseMove that you can think would work?

Many thanks,
D€$
 
Upvote 0 Downvote
Well, I had a look at that but don't have the expertise to figure it out. Our network server has crashed so we're dead in the water. Anyway I'll see what I can do on my Desktop.

Many thanks,
D€$
 
Upvote 0 Downvote
Well, this works on my 'C' drive on a test workbook; I'll have to try over the network (if that makes any difference) when it's back up.

Code: 
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_data As Variant
Dim chart_label As Variant
Dim chrt As Chart
Dim ser As Series

Set chrt = ActiveChart

chrt.GetChartElement x, y, ElementID, Arg1, Arg2
Set ser = chrt.SeriesCollection(1)

chart_data = ser.Values
chart_label = ser.XValues

Select Case ElementID
       Case xlSeries
          If Arg1 = "1" And Arg2 <> "1" Then 'Series 1 but not Summary
    
        ' Promt
          strPrompt = "Do you want to review the data for:" & Chr$(13) & _
          chart_label(Arg2) & "?"
     
        ' Dialog's Title
          strTitle = "Review Data for: " & chart_label(Arg2)
     
        'Display MessageBox
          iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)
     
        ' Check pressed button
          If iRet = vbYes Then
             Worksheets(chart_label(Arg2)).Activate
          End If
          
          End If
    
   End Select

   Set chrt = Nothing
   Set ser = Nothing

End Sub

Many thanks,
D€$
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
338
Andrzejek
Andrzejek
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
899
dhookom
dhookom
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
632
strongm
strongm
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
504
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top