Text-wrapping horizontal axis labels

  • Follow


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  
0
Reply Utf 3/29/2010 4:41:01 PM

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,
> 
> 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


-- 
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

0
Reply teylyn 3/30/2010 11:35:48 AM

"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:
> 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,
>>
>> 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
>
>
0
Reply Jon 3/30/2010 10:53:43 PM

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
[/code]

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:

On Monday, March 29, 2010 12:41 PM
LAird wrote:

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

On Tuesday, March 30, 2010 7:35 AM
teylyn wrote:

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

On Tuesday, March 30, 2010 6:53 PM
Jon Peltier wrote:

"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/tutorials/aspnet/0939d60c-8e17-4a27-b898-1fc772d2d6f6/scrolling-in-wpf-toolkits-column-chart.aspx
0
Reply theradpotato (2) 7/26/2010 11:21:06 PM

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
[/code]

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:

On Monday, March 29, 2010 12:41 PM
LAird wrote:

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

On Tuesday, March 30, 2010 7:35 AM
teylyn wrote:

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

On Tuesday, March 30, 2010 6:53 PM
Jon Peltier wrote:

"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:

On Monday, July 26, 2010 7:20 PM
Joe Omundson 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
[/code]

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/tutorials/aspnet/6b1df1e9-e8fc-4dc1-a810-1245a8ef0208/biztalk-parallel-processing-with-correlation.aspx
0
Reply theradpotato (2) 7/26/2010 11:22:05 PM

4 Replies
1811 Views

(page loaded in 0.082 seconds)


Reply: