Text-wrapping horizontal axis labels


L

LAird

Hi,

I've a small problem which I can't seem to find a solution for - I have
created a set of financial reports for a client, which includes a number of
charts. The charts are waterfall charts, which I've constructed using a form
of stacked bar chart. The labels for the horizontal axis are linked to text
alongside the calculations for the charts.

The text in the labels is of varying lengths and for some of the charts,
this text is being wrapped to two layers on the label that appear on the
chart. Every now and then, one of the labels is too long, and instead of
wrapping to three layers (as I would expect it to) the text is not wrapped at
all, on any of the labels, and so simply overlaps all the other labels.

I know that lengthening the axis would help, but the size of the graph is
standardised so that the reports are visually consistent. Also, I'm reluctant
to change the font size of the label for the same reason. A manual carriage
return in the cell containing the text for the label will accomplish wrapping
to three layers for me, but it would seem to me that there should be a
control for text-wrapping on axis labels that I use to change this.

Does anyone have any ideas?

Thanks,
LAird
 
Ad

Advertisements

J

Jon Peltier

"XXX stubbornly resists any kind of human intervention"

Unfortunately so many elements of Excel charting can complete this sentence.

- Jon
 
J

Joe Omundson

Here's what I ended up doing:
Code:
LastRows = Application.WorksheetFunction.CountA _
(Sheets(tname).Range("K:K"))
For a = 1 To LastRows
Range("K" & a).Select
If Len(Selection.Value) > 65 Then Selection.Value _
= Left(Selection.Value, 60)
For b = 1 To Int(Len(Selection.Value) / 12)
Selection.Value = Left(Selection.Value, b * 13 _
- 1) & Chr(10) & Right(Selection.Value, _
Len(Selection.Value) - b * 13 + 1)
Next b
Next a
Rows("1:2000").RowHeight = 15

Basically I cycle through all the category labels (I have them in K1:K10 or whatever) and insert a return in the text after every 12 characters, so it's wrapped in effect. The downside is that it will split words across the return, but I was desperate. Maybe with more work you could have it look for the next space character and tell it to return if there will be more than N characters on that line.

To change the number of characters per line, change "12" to N and "13" to N+1... that should work.

Good luck,
-Joe



LAird wrote:

Text-wrapping horizontal axis labels
29-Mar-10

Hi,

I have a small problem which I cannot seem to find a solution for - I have
created a set of financial reports for a client, which includes a number of
charts. The charts are waterfall charts, which I have constructed using a form
of stacked bar chart. The labels for the horizontal axis are linked to text
alongside the calculations for the charts.

The text in the labels is of varying lengths and for some of the charts,
this text is being wrapped to two layers on the label that appear on the
chart. Every now and then, one of the labels is too long, and instead of
wrapping to three layers (as I would expect it to) the text is not wrapped at
all, on any of the labels, and so simply overlaps all the other labels.

I know that lengthening the axis would help, but the size of the graph is
standardised so that the reports are visually consistent. Also, I am reluctant
to change the font size of the label for the same reason. A manual carriage
return in the cell containing the text for the label will accomplish wrapping
to three layers for me, but it would seem to me that there ishould be a
control for text-wrapping on axis labels that I use to change this.

Does anyone have any ideas?

Thanks,
LAird

Previous Posts In This Thread:

Text-wrapping horizontal axis labels
Hi,

I have a small problem which I cannot seem to find a solution for - I have
created a set of financial reports for a client, which includes a number of
charts. The charts are waterfall charts, which I have constructed using a form
of stacked bar chart. The labels for the horizontal axis are linked to text
alongside the calculations for the charts.

The text in the labels is of varying lengths and for some of the charts,
this text is being wrapped to two layers on the label that appear on the
chart. Every now and then, one of the labels is too long, and instead of
wrapping to three layers (as I would expect it to) the text is not wrapped at
all, on any of the labels, and so simply overlaps all the other labels.

I know that lengthening the axis would help, but the size of the graph is
standardised so that the reports are visually consistent. Also, I am reluctant
to change the font size of the label for the same reason. A manual carriage
return in the cell containing the text for the label will accomplish wrapping
to three layers for me, but it would seem to me that there ishould be a
control for text-wrapping on axis labels that I use to change this.

Does anyone have any ideas?

Thanks,
LAird

Hi LAird,unfortunately, axis labels stubbornly resist any kind of
Hi LAird,

unfortunately, axis labels stubbornly resist any kind of human
intervention when it comes to resizing the label boxes or the wrapping.

This has been an annoyance for a long time.

cheers, teylyn


LAird;684976 Wrote:
Hi,
have
number of
form
text
charts,
the
of
wrapped at
labels.
is
reluctant
carriage
wrapping


--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=191426

http://www.thecodecage.com/forumz

"XXX stubbornly resists any kind of human intervention"Unfortunately so many
"XXX stubbornly resists any kind of human intervention"

Unfortunately so many elements of Excel charting can complete this sentence.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/30/2010 7:35 AM, teylyn wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Scrolling in WPF Toolkit?s Column Chart
http://www.eggheadcafe.com/tutorial...6/scrolling-in-wpf-toolkits-column-chart.aspx
 
J

Joe Omundson

Here's what I ended up doing:
Code:
LastRows = Application.WorksheetFunction.CountA _
(Sheets(tname).Range("K:K"))
For a = 1 To LastRows
Range("K" & a).Select
If Len(Selection.Value) > 65 Then Selection.Value _
= Left(Selection.Value, 60)
For b = 1 To Int(Len(Selection.Value) / 12)
Selection.Value = Left(Selection.Value, b * 13 _
- 1) & Chr(10) & Right(Selection.Value, _
Len(Selection.Value) - b * 13 + 1)
Next b
Next a
Rows("1:2000").RowHeight = 15

Basically I cycle through all the category labels (I have them in K1:K10 or whatever) and insert a return in the text after every 12 characters, so it's wrapped in effect. The downside is that it will split words across the return, but I was desperate. Maybe with more work you could have it look for the next space character and tell it to return if there will be more than N characters on that line.

To change the number of characters per line, change "12" to N and "13" to N+1... that should work.

Good luck,
-Joe



LAird wrote:

Text-wrapping horizontal axis labels
29-Mar-10

Hi

I have a small problem which I cannot seem to find a solution for - I hav
created a set of financial reports for a client, which includes a number o
charts. The charts are waterfall charts, which I have constructed using a for
of stacked bar chart. The labels for the horizontal axis are linked to tex
alongside the calculations for the charts

The text in the labels is of varying lengths and for some of the charts
this text is being wrapped to two layers on the label that appear on th
chart. Every now and then, one of the labels is too long, and instead o
wrapping to three layers (as I would expect it to) the text is not wrapped a
all, on any of the labels, and so simply overlaps all the other labels

I know that lengthening the axis would help, but the size of the graph i
standardised so that the reports are visually consistent. Also, I am reluctan
to change the font size of the label for the same reason. A manual carriag
return in the cell containing the text for the label will accomplish wrappin
to three layers for me, but it would seem to me that there ishould be
control for text-wrapping on axis labels that I use to change this

Does anyone have any ideas

Thanks
LAird

Previous Posts In This Thread:

Text-wrapping horizontal axis labels
Hi

I have a small problem which I cannot seem to find a solution for - I hav
created a set of financial reports for a client, which includes a number o
charts. The charts are waterfall charts, which I have constructed using a for
of stacked bar chart. The labels for the horizontal axis are linked to tex
alongside the calculations for the charts

The text in the labels is of varying lengths and for some of the charts
this text is being wrapped to two layers on the label that appear on th
chart. Every now and then, one of the labels is too long, and instead o
wrapping to three layers (as I would expect it to) the text is not wrapped a
all, on any of the labels, and so simply overlaps all the other labels

I know that lengthening the axis would help, but the size of the graph i
standardised so that the reports are visually consistent. Also, I am reluctan
to change the font size of the label for the same reason. A manual carriag
return in the cell containing the text for the label will accomplish wrappin
to three layers for me, but it would seem to me that there ishould be
control for text-wrapping on axis labels that I use to change this

Does anyone have any ideas

Thanks
LAird

Hi LAird,unfortunately, axis labels stubbornly resist any kind of
Hi LAird

unfortunately, axis labels stubbornly resist any kind of huma
intervention when it comes to resizing the label boxes or the wrapping

This has been an annoyance for a long time

cheers, teyly

LAird;684976 Wrote
Hi
hav
number o
for
tex
charts
th
o
wrapped a
labels
is
reluctant
carriage
wrapping


--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=191426

http://www.thecodecage.com/forumz

"XXX stubbornly resists any kind of human intervention"Unfortunately so many
"XXX stubbornly resists any kind of human intervention"

Unfortunately so many elements of Excel charting can complete this sentence.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/30/2010 7:35 AM, teylyn wrote:

Crude solution
Here's what I ended up doing:
Code:
LastRows = Application.WorksheetFunction.CountA _
(Sheets(tname).Range("K:K"))
For a = 1 To LastRows
Range("K" & a).Select
If Len(Selection.Value) > 65 Then Selection.Value _
= Left(Selection.Value, 60)
For b = 1 To Int(Len(Selection.Value) / 12)
Selection.Value = Left(Selection.Value, b * 13 _
- 1) & Chr(10) & Right(Selection.Value, _
Len(Selection.Value) - b * 13 + 1)
Next b
Next a
Rows("1:2000").RowHeight = 15

Basically I cycle through all the category labels (I have them in K1:K10 or whatever) and insert a return in the text after every 12 characters, so it's wrapped in effect. The downside is that it will split words across the return, but I was desperate. Maybe with more work you could have it look for the next space character and tell it to return if there will be more than N characters on that line.

To change the number of characters per line, change "12" to N and "13" to N+1... that should work.

Good luck,
-Joe


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Parallel Processing with Correlation
http://www.eggheadcafe.com/tutorial...alk-parallel-processing-with-correlation.aspx
 
Ad

Advertisements

Joined
Mar 8, 2012
Messages
1
Reaction score
0
You can resize the entire chart so it's bigger vertically. This will give enough room for the wrapped labels. You can then resize the inside "plot area" to make it smaller vertically if it doesn't fit in the area you want or you want to match the size to other charts.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top