Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
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 On Thu, 6 Dec 2007 10:53:46 -0000, "Bob Phillips" wrote: 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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
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(.SeriesCollect ion(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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Brian Reilly, MVP" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
XL Axis label formatting issue
Andy,
I'm uh. . . speechless. You rascal. Thank you. Brian Reilly, PowerPoint MVP On Tue, 11 Dec 2007 09:18:58 -0000, "Andy Pope" wrote: 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(.SeriesCollec tion(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Y-axis label Bug | Charts and Charting in Excel | |||
Y-Axis Label | Charts and Charting in Excel | |||
X axis label problem | Charts and Charting in Excel | |||
how do i label the axis? | Charts and Charting in Excel | |||
how to remove label formatting (eg label to number) | Excel Worksheet Functions |