Category labels on X-axis of scatter chart - axis title won't let me make it wide enough

[RuthB]

Hi Everyone.

You were so helpful to me last time I was stuck that I'm now back for more help!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am using Excel 2007 to collate and analyse students' exam results in my school. One way my department likes to display them is on an XY Scatter chart with error bars. I am using X values of 1, 2, 3 etc to represent classes, Y values to represent exam scores of kids in the classes, and error bars to indicate where there is more than one student in the same class with the same score. Therefore each class is represented as a vertical column of data points with horizontal error bars, and it's easy to see the distribution of results in each class. (I hope I've explained that so it makes sense.)<o></o>
<o></o>
It all works fine, apart from the axis title. The X values have to be numbers or it won't let me do an XY Scatter chart, but of course the numbers aren't what I want to display along the X-axis - I want the names of the classes. I've set the axis not to have any tick mark labels, and am trying to put the class names on some other way (with a macro that will change them when I set up the graph for a new year group).<o></o>
<o></o>
The first way I tried was with the axis title. I formatted the axis title in a fixed width font, and then wrote a macro to count up the characters in all the names of the classes and work out how many spaces should go between the class names to spread them out correctly, and so create a text string to go in the axis title. It works all right when there are only 3 or 4 classes (apart from the fact that if I change it by hand then the box stays centred on the page, but if the macro changes it then it doesn't).

However, some year groups have 8 classes (and the classes have names of several characters), so I need the axis title to go almost all the way to the edge of the chart, so that the 1st and last class names will line up with their results. It won't let me make the axis title as wide as this. As soon as I get it not quite wide enough, Excel fixes the edges of the box and goes onto a new line within the box.
<o></o>
Then I tried inserting a textbox (not a control, just the sort of thing you get on the Insert tab of Excel without going into the VB editor or the developer tab). That works fine by hand, but I can't work out how to refer to the textbox in my VB code in order to get the macro to put my caption string into it.<o></o>
<o></o>
Can anyone suggest anything I could try?<o></o>
<o></o>
Thanks loads.<o></o>

 

[pgc01]

Hi RuthB

If I understand correctly you just have to add a new series with Y=0 and X=1,2,3...

Since Y=0, this new series will be on the X axis, and it's points will be where you want the classes names. Add labels to the points, position below, and write them the classes names.

 

[RuthB]

Hi RuthB

If I understand correctly you just have to add a new series with Y=0 and X=1,2,3...

Since Y=0, this new series will be on the X axis, and it's points will be where you want the classes names. Add labels to the points, position below, and write them the classes names.

Thanks pgc01

I tried that. The problem is that I don't want to have to do it manually - I want it to be part of the VBA code, and I can't find any way to add automatic labels that aren't the x or y values.

What I really want to do it plot a data series arising from 3 columns on the worksheet - one column for x-values, one column for y-values, and a third column for the data labels for each point. Is there any way of doing this in VBA?

So far I have only been able to do it by defining each point as a separate series, and then I can label it with its series name. It's rather cumbersome, though. I would like to have just one series, with different labels for each point.

Thanks.

 

[RuthB]

It's OK. I've sorted it.

You can do it with SeriesCollection(1).Points.DataLabel.Text

 

[pgc01]

I'm glad you sorted it out. I had an example that I would post but I'm glad it's not needed.Cheers.