(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

 

[Andrew Poulsom]

Did you put this part in the module for the worksheet?

' ** Sheet module code to connect to the Class module **

Dim MyChart As New Class1

Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub

You will need to deactivate and reactivate the sheet to get it going.

 

[phil152003]

Yes I did include that code. So if I save Excel, close everything down, and re-load the workbook it should then work?

 

[Andrew Poulsom]

Where did you put the code? It goes in the module for the worksheet not in the Class module. To get it to fire activate another sheet then reactivate the sheet containing the chart.

 

[phil152003]

Sorry for my constant questions, but what do you mean by "activate"?

Also, your saying that I didn't need to do insert > class module then? I just select the worksheet that the graph is in from the left in the vba editor, and paste the code into the blank screen that appears?

 

[Andrew Poulsom]

Yes, you do need to insert a class module. But not all of the code goes in there. The part I posted above goes in the module for the worksheet.

To connect your chart with the class activate another worksheet (by clicking its tab) then return to the sheet containing the chart.

 

[phil152003]

Ah, I feel so stupid now! Thanks for that, I've got it going. The labels are even working correctly so thanks for your help! However, I do have another problem.

The chart in question has four different series, each with its own set of unique labels for each data point. Now, obviously just using the code you posted, the data labels that appear in the popup box for each series are going to be the labels for whichever column I enter into the VBA editor. I need a way so that the VBA editor asks itself:

IF I have clicked on series1, SHOW this set of labels, OTHERWISE, IF I have clicked series2, SHOW this set of labels, etc.

I have no idea how this would be implemented though. If you could help me out here that would be great.

 

[phil152003]

After a little playing, I actuall figured this out myself!

Instead of, in the labels part of the code having ...cells(b,1)... I changed it to ...Cells(b, 3 * a - 2).Value... which corresponded with the way that my data was set out.

It is set out in a table 12 columns wide, consisting of 4 sets of three columns: column 1 being the labels, 2 and 3 being the X and Y coordinates (for each of the 4 series). I assumed "a" was the series number, so the labels for series one appears in column 3*1-2=1, labels for column 2 appear in column 3*2-2=4 etc.

Anyway, I appear to have fixed it. Thankyou very much for your help!

 

[phil152003]

Actually, I'm having a couple more issue with the chart now. I wonder if you'd be able to help me out.

I inserted the code posted earlier into my code to disable right click / double click. And it hasn't done anything! i.e. right click / double click still work as normal.

The code:

Public WithEvents Ch As Chart
Dim IDNum As Long
Dim a As Long
Dim b As Long
Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    Application.ScreenUpdating = False
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = Worksheets("Chart Calculation - RV & Hide").Range("AC8:AL676").Cells(b, 3 * a - 2).Value
            With .DataLabel
                .Text = Txt
                .Position = xlLabelPositionAbove
                .Font.Size = 20
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub
Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
            Application.ScreenUpdating = True
        End With
    End If
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

Can you spot what I'm doing wrong?

Secondly, my chart has been spilt into 4 quadrants. I've placed a textbox in each quadrant with a word that describes what a point in that quadrant means. However, when a data point hovers over the textbox, when I go to click the datapoint (to look at the popup label) it selects the textbox instead. Is there a way in vba to disable clicking the textboxes? I'm also having the same issue with the autoshape lines that I've used to create the quadrants with.

I googled this, and have seen a couple of answers, but I can't get them to work. If you could tell me exacty how to do this, and where exacty I should paste the code in the VBA editor then that would be fantastic!

 

[phil152003]

And finally, one more problem. It works exactly as I need it to in Excel 2003 (with the exception of the text boxes / autoshapes being clicked which I hope you can help me with!

However, if I open the file up in Excel 2007 (which some of the people viewing the file when it's complete will be using), it appears that the mouseup command isn't working properly. The label appears, exactly as it should which is great. However, it remains there, even after I've unclicked. In fact, the only way to remove it is to select the label with the mouse and press delete. This is very awkard (in what is supposed to be a very profession looking file) than in the 03 version, and if you've any ideas how this could be fixed, then again that would be greatly appriciated.

Thankyou very much for your help!

 

[Andrew Poulsom]

I inserted the code posted earlier into my code to disable right click / double click. And it hasn't done anything! i.e. right click / double click still work as normal.

The code (snipped):

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

Can you spot what I'm doing wrong?

It should be:

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