(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

 

[phil152003]

Ah that worked, thanks! Also, any suggests as to why it isn't working 100% correctly in Excel 2007?

 

[Andrew Poulsom]

I don't have access to Excel 2007 where I am at the moment. I'll try an have a look later.

 

[phil152003]

I appriciate that, thankyou.

 

[phil152003]

Sorry to a pain, one more thing. I'm suffering from some pretty bad flickering when I click the points. You said earlier

"1. Application.ScreenUpdating = False at the beginning of the code and Application.ScreenUpdating = True at the end might help with the flicker.
"

Could you tell me exactly where in my existing code that should be put? I've tried putting it in a couple of places but it doesn't seem to do anything! Thanks.

 

[RoryA]

FYI, I seem to recall Jon P mentioning that the MouseUp and MouseDown events don't trigger properly in 2007.

 

[phil152003]

Ah that's annoying. And I guess a solution wasn't found?

 

[Andrew Poulsom]

He says he's figured out a few workarounds, but hasn't had time to really apply them:

http://peltiertech.com/WordPress/pts-charts-in-excel-2010/

 

[Diego_Jose]

Andrew, I have been looking for a solution like this all over the internet, so far yours is the best implementation. I just have a problem with the mouse button click speed; if it is too fast it will give an error message (a debug shows that the error is the b value going to -1). I don't know if it is in fact an error or the code is written so you are supposed to leave the button pressed.

 

[Andrew Poulsom]

Sorry, I don't know why that would happen.

 

[phil152003]

Andrew, I have one more little query.

The popup label is working fantastically well, but there is one slight issue - when I click on a datapoint near or on the y axis of the scatter chart, when the label pops up it overlaps with the axis (not a problem), but the axis values and axis label appear on top of the label. This creats a jumble of axis values and what the popup label says.

I was wondering if there was any sort of code I could add that would make the popup label the top layer on the chart, meaning that when it pops up after clicking on a point, anything that it overlaps appears behind the label (so you can't see it).

Its worth mentioning that this is only an issue with axis values and labels. When the label pops up and overlaps with another datapoint for example, the datapoint ends up behind the label so you can't see it until you release the mouse click (as I am wanting).

If you can help at all that would be great.

Thanks.