XL Axis label formatting issue

[Brian Reilly, MVP]

Hi folks, I know that has been asked a million gazillion time but
can't chase down a good solution, or missed it for an XL solution.
Forget MS Grump.

The labels in Axes have problems with line breaks. Is there a work
around via VBA?

The font labels might want to change color. e.g. Label 1 good =green
and then Label 2 bad =red.

I don't think this can be done, but if it can be done, I'd bet, in
alphabetical sort that And P or Jon P could do this.

anyone have any ideas how to do this?

Thanks to all,

Brian Reilly, PPT MVP

 

[Bob Phillips]

Brian,

What you have to do is create a table with a couple of extra ranges. Let's
say that the labels are in A2:A10, amounts in B2:B10 and you want to
highlight on a condition

C2: =IF(condition_met,0,NA())
D2: =IF(condition_not_met,0,NA())

Then select the whole range and chart it. This will create a chart with 3
series.

Find the series 2 series and right-click it and select Chart Type and change
it to line.

Then Double click the data series and change check the Category Name on the
DataLabels tab.

Then right-click the data labels and on the Alignment tab, change the Label
Position to below. Yu can also set the font here.

Repeat for series 3.

You might want to remove the colour from the series 2 and 3 lines as well.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Brian Reilly, MVP]

Bob,
Thanks for the response. I was out of the Office all day today but saw
you msg and will test it in next couple of days. Sounds like it should
work.

Brian Reilly, PPT MVP

 

[Brian Reilly, MVP]

More on this issue (XL 2003).

The Client has supplied a simple example that I can suppy but here's
the explanation.

The chart on the left has the stub (that's Y axis) as part of the
chart – everything is centered instead of right aligned, the third
label is truncated because it’s too long and we can’t format
individual words within the stub. When we bring the stub into a text
box (on the right side), all of these problems are resolved.

I found a way to stretch the Y-axis values and to right align but
can't find those again. I think the biggest challenge is to format
individual words on the Y-Axis with simple Properties such as Font,
Color, underscored etc.

They use textboxes and turn the Y-axis off which I know how to do. But
how would one know how to align the text in the text box to "match" up
with the data (these are bar charts)? Can this sort of be done (VBA
solution, of course) with a calculation involving #data series and
PlotArea.Height?

Thanks for any input.

Brian Reilly, PowerPoint MVP

 

[Andy Pope]

Hi Brian,

Yes it can.
Create a bar chart as normal. Turn default Y axis labels of and position the
plot area so you have the required space to the left of the plot area.
The following will remove existing shapes within the chartobject and add new
ones.

'------------------------------
Sub AddAxisLabels()
'
' Add Textboxes to chart in order to replace Y axis labels
'
Dim chtTemp As Chart
Dim shpAxisLabel As Shape
Dim sngHeight As Single
Dim sngTop As Single
Dim sngLeft As Single
Dim sngWidth As Single
Dim lngPoint As Long

Set chtTemp = ActiveSheet.ChartObjects(1).Chart

With chtTemp
' remove any embedded shapes
Do While .Shapes.Count > 0
.Shapes(1).Delete
Loop

sngLeft = .ChartArea.Left
sngWidth = .PlotArea.InsideLeft - .ChartArea.Left
sngHeight = .PlotArea.InsideHeight /
..SeriesCollection(1).Points.Count

If .Axes(xlCategory, xlPrimary).ReversePlotOrder Then
' From Top down
sngTop = .PlotArea.InsideTop
Else
' from bottom up
sngHeight = sngHeight * -1
sngTop = .PlotArea.InsideHeight + .PlotArea.InsideTop +
sngHeight
End If

For lngPoint = 1 To .SeriesCollection(1).Points.Count
Set shpAxisLabel =
..Shapes.AddTextbox(msoTextOrientationHorizontal, sngLeft, sngTop, sngWidth,
Abs(sngHeight))
shpAxisLabel.TextFrame.Characters.Text =
Application.WorksheetFunction.Index(.SeriesCollection(1).XValues, lngPoint)
With shpAxisLabel.TextFrame
' format textbox as required.
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.AutoSize = False
.Orientation = msoTextOrientationHorizontal
End With
sngTop = sngTop + sngHeight
Next

End With

End Sub
'------------------------------

If you need example workbook let me know.

Cheers
Andy

 

[Brian Reilly, MVP]

Andy,
I'm uh. . . speechless. You rascal.
Thank you.
Brian Reilly, PowerPoint MVP