(x,y) scatter plot label vba implementation question

[semiclassical]

Problem:

I have a scatter plot with 150 points closely grouped. Enabling the label option clutters the graph. A cleaner way would be to hoveri the mouse over a data point and pop-up a box displaying it's value (x,y) as well as a label contained in an adjacent column.

Details:

The default functionality is that when the mouse hovers over a particular point the yellow pop-up box appears with

• 
  Data set label
  Data point name "x"
  Data point (x,y)

and one can choose to show "label-name-point" or just "point" by accessing the Tools-Options_Charts menu item. What I would like instead is to hover the mouse over a particular point and get the yellow pop-up box with the following functionality

• 
  Data set label
  Data point name "label"
  Data point (x,y)

where the label is contained in an adjacent column in my data spreadsheet.

I read a post by Andrew Poulsom that stated it is probably not possible to customize the yellow pop-up baox and then offered some vba code to allow a mouse click to bring up a label. I was wondering how to implement this code in the spreadsheet. Also, does the graph have to be embedded in the worksheet "as object in", or can I use "As new sheet"?

Any help appreciated...

Aaron

 

[semiclassical]

For example, going back to before the addition of the code, when you right click on a data point it will select that data set and then double clicking brings up "Format Data Series". Can I disable this ...

 

[Andrew Poulsom]

Add this code:

Private Sub Chart_BeforeRightClick(Cancel As Boolean)
    Cancel = True
End Sub

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Cancel = True
End Sub

That disables right click and double click.

 

[semiclassical]

Again, thanks sooo much. I was really at a loss and this has helped greatly!

Take care....

 

[oliveira.nicolas]

Hello Andrew,

I'm new to Excel and I'm far from being proficient with it... I would be very interested in such a labeling tool as the one described in the first message. I've checked out office support and have found the Macro for labelling XY charts. My problem is that my data is becoming unreadable when labels are displayed. Sorry to ask this but could you provide me some info to do what is probably already described her?

Cheers

 

[PharmBoy]

Well, first-off, this code has been an amazingly large help to me; however, I've run into some instances of trouble with it - most of which are sorted out. I just have one last hitch in my giddy-up!

The issue is that I've modified the code such that it will post the data label when the point is clicked and then when the data label is clicked, it will popup a MsgBox with an expanded definition of what the data label is - herein lies the problem. When I click the data label, the MsgBox pops up, as it should, but once I click OK on said box, the data label begins to follow my mouse as if I'm trying to move it.

So, my question is, what do I need to add to the code in order to "deselect" the label or cancel the moving of it after the MsgBox is acknowledged?

Thanks in advance!

Dim Txt As String
Dim Def As String
Txt = vbNullString
Def = vbNullString
    With ActiveChart
        .GetChartElement x, y, IDNum, a, b
        If IDNum = xlSeries Then
            With .SeriesCollection(a).Points(b)
                 .HasDataLabel = True
                Txt = Sheets(5).Range("A4:A32003").Cells(b, 1).Value
                With .DataLabel
                    .Text = Txt
                    .Position = xlLabelPositionAbove
                    .Font.Size = 8
                    .Border.Weight = xlHairline
                    .Border.LineStyle = xlAutomatic
                    .Interior.ColorIndex = 19
                End With
            End With
        End If
        If IDNum = xlDataLabel Then
            With .SeriesCollection(a).Points(b)
                Def = Sheets(5).Range("D4:D32003").Cells(b, 1).Value
                MsgBox Def
                Txt = Sheets(5).Range("A4:A32003").Cells(b, 1).Value
                With .DataLabel
                    .Text = Txt
                    .Position = xlLabelPositionAbove
                    .Font.Size = 8
                    .Border.Weight = xlHairline
                    .Border.LineStyle = xlAutomatic
                    .Interior.ColorIndex = 19
                End With
            End With
        End If
    End With
End Sub
Private Sub Chart_BeforeRightClick(Cancel As Boolean)
    Cancel = True
End Sub
Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Cancel = True
End Sub

 

[Andrew Poulsom]

I can't reproduce that behaviour in Excel 2003. The data label is selected but it doesn't move with the mouse. I am assuming the code you posted is in the Chart_MouseDown event ptocedure.

You could try selecting eg the PlotArea in your code.

 

[PharmBoy]

When I disabled the MsgBox component I was able to discover the root of the problem, which is, seemingly that by having the MsgBox "stop" after the click on the DataLabel, it never registers that the mouse button has been released. So, the DataLabel trails the mouse around as if the button has been held down. Once I sat and thought for a moment, drank a couple cups of coffee, visited the gym to vent frustrations and after having tried nearly every derrivation of expression.Select after the MsgBox command, it dawned on me to go back to the original code and rather than have it take away the DataLabel, have it pop up the MsgBox. Problem solved!​

Below is the code, in case anyone else reading this cares to do something similar!​

[LEFT]Dim IDNum As Long
Dim a As Long
Dim b As Long
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Txt As String
Txt = vbNullString
  With ActiveChart
      .GetChartElement x, y, IDNum, a, b
      If IDNum = xlSeries Then
          With .SeriesCollection(a).Points(b)
               .HasDataLabel = True
              Txt = Sheets(5).Range("A4:A32003").Cells(b, 1).Value
              With .DataLabel
                  .Text = Txt
                  .Position = xlLabelPositionAbove
                  .Font.Size = 8
                  .Border.Weight = xlHairline
                  .Border.LineStyle = xlAutomatic
                  .Interior.ColorIndex = 19
              End With
          End With
      End If
  End With
End Sub
Private Sub Chart_BeforeRightClick(Cancel As Boolean)
  Cancel = True
End Sub
Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
  Cancel = True
End Sub
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Def As String
Def = vbNullString
  With ActiveChart
      .GetChartElement x, y, IDNum, a, b
      If IDNum = xlDataLabel Then
              Def = Sheets(5).Range("D4:D32003").Cells(b, 1).Value
              MsgBox Def, vbOKOnly + vbInformation, "Definition"
      End If
  End With
End Sub[/LEFT]

 

[phil152003]

Sorry to revive this old thread, but I’m really getting stuck here.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m not going to pretend that I know the first thing about VBA, but I have this exact problem and it looks like these posts should solve it. However, I just can’t get it to work. This is what I’ve done:
<o> </o>
I already have a scatter graph chart (Excel 2003) on worksheet “Summary”.
I go into the VBA editor
Insert > class module
Copy and paste the code from the link posted in the original post (http://www.mrexcel.com/forum/showthread.php?t=45004)
I save
Go back to my Excel sheet… and nothing happens, it doesn’t look like the code made any difference to the chart whatsoever.
<o> </o>
Now I must be missing something obvious, and as I say this is the first time I’ve ever had to use VBA. If anyone can help me at all with some basic instructions as to how I get this code to work then that would be greatly appreciated!]
<o> </o>
Thanks!

 

[PharmBoy]

Where did you paste the code? I assume you pasted it to the Chart and not the Spreadsheet? Also, are you certain that your references for the labels are correct?

You may also try posting an example workbook to allow others to play with this a bit.

 

[phil152003]

I thought the reference to the labels was right. But its not even coming up with anyothing when I click on a data point which makes me think that isnt the issue.

As to where I pasted it, I just opened VBA editor from the spreadsheet (in which my chart is an object in), and then pressed insert > class module, and paated it into the blank page that appears. Apologies if this is the incorrect thing to do, and if you could point me in the right direction that would be great.

I'll paste an example spreadsheet when i get back to work on monday.

Thanks fir your response!