Labels for horizontal points

[ArtZ]

Hi,

I have a worksheet that uses VBA code to label data points. (many thanks to Jon Peltier) It works fine for the vertical data points, however, there are data points along the x axis where the y value =0. These labels should be placed above these data points, not to the right.

Also, if the first x axis data were placed above that point, it would run into the y data point above. I need a solution for that as well.

Could anyone in the Forum modify the VBA code to fix these problems?

Any help is greatly appreciated.

Thanks,

Art

[Crooza]

If you change this line from this

.Position = xlLabelPositionright to this

.Position = xlLabelPositiontop you'll change the labelling position. The macro gives you the choice to be selective in which labels you choose so I changed the macro and ran it just on the one's I wanted to be above and it worked fine.

If you want the macro to automatically change to right or top depending on it's location to other points then that's going to be a bit more coding

[ArtZ]

Hi Crooza,

Thanks for your response and comments. In looking at the chart again, I realize that I need some some labels to the right, some to the left and some above based on the chart data. Do you know how to modify the VBA code to allow selection of label placement based on data range?

Thanks,

Art

[Crooza]

OK. I haven't forgotten you. There's a bit of coding required to do this. I thought there was a xlLabelpositionbestfit but doesn't seem to work or be available to me so I've worked out a way to try and best fit all the labels so they are placed in the location where there is the greatest space.

To do this I used some helper columns and ideally in time I'll get around to hardcoding these helper columns into the macro but in the meantime I'm using 4 columns to do some array calculations to find where the nearest other points are in relation to the reference points location (ie above, below, right or left of the reference point). I then look for the greatest space and place the text on that side. It doesn't take into account where text from other points might interfere so it is very basic but it works to a degree. I tried it on both your data (attached) and also on some random data sets and it does appear in most cases to 'clean up' the labelling.

Try the attached file and run the macro for yourself. If and when I get all the helper columns hardcoded I'll upload that too.