?
Solved

interact with point's label

Posted on 2011-10-17
3
Medium Priority
?
202 Views
Last Modified: 2012-05-12
Guys,

The attached xls file has a macro which enables me to do an action when i click on any point/marker on the xychart. What i want now is to do the same this for the label in which if i click on it ,it will do the same action that is assigned when i click on the point/marker. Usually when someone clicks on the label, the text in it will be highlighted to enable you to edit it, so I don't know how we can come over this.

chartevents3.xls
0
Comment
Question by:bsaisu
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 180 total points
ID: 36978134
Try this version of the Select event in your class:
Private Sub objChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

   Dim s                 As Excel.Series
   Dim p                 As Excel.Point

   On Error GoTo err_handler
   Application.EnableEvents = False
   If ElementID = xlSeries Or ElementID = xlDataLabel Then
      If TypeName(Selection) <> "DataLabels" Then

         Set s = objChart.SeriesCollection(Arg1)
         Debug.Print Arg2
         If Arg2 > -1 Then
            Set p = s.Points(Arg2)
            p.Select
         Else
            Set p = s.Points(1)
            Application.EnableEvents = False
            s.Select
            Arg2 = 1
         End If
         MsgBox s.Name & vbTab & s.XValues(Arg2) & vbTab & s.Values(Arg2)
      End If
   End If

clean_up:
   Application.EnableEvents = True
   Exit Sub

err_handler:
   MsgBox Err.Description
   Resume clean_up


End Sub

Open in new window

0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 320 total points
ID: 36978160
Try this to replace your Chart_Select routine:
Private Sub objChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

    Dim s As Excel.Series
    Dim p As Excel.Point
    Dim d As Excel.DataLabel
    
    Select Case ElementID
    
        Case xlSeries
    
            Set s = objChart.SeriesCollection(Arg1)
            Debug.Print Arg2
            If Arg2 > -1 Then
                Set p = s.Points(Arg2)
                p.Select
            Else
                Set p = s.Points(1)
                s.Select
                Arg2 = 1
            End If
            MsgBox s.Name & vbTab & s.XValues(Arg2) & vbTab & s.Values(Arg2)
            
        Case xlDataLabel
    
            Set s = objChart.SeriesCollection(Arg1)
            Debug.Print Arg2
            If Arg2 > 0 Then
                Set d = s.DataLabels(Arg2)
                d.Select
            Else
                Set d = s.DataLabels(1)
                s.DataLabels.Select
                Arg2 = 1
            End If
            MsgBox s.Name & vbTab & s.XValues(Arg2) & vbTab & s.Values(Arg2)
    End Select
     
End Sub

Open in new window

0
 

Author Comment

by:bsaisu
ID: 36978566
Guys both of your solution is working but i think I would go with andrewssd3's solution because it seems slightly better.
rorya, your solution is good but in your solution I have to click on the point or the label twice to trigger the event. andrewssd3 has fixed this

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question