Printing Text Boxes in a Chart Correctly

[chuck]

Hello:
How does one get long text boxes created in a chart to
print preview and/or print as seen on the screen? When a
long text box is created in a chart, you can set-it-up so
that it is lined-up exactly as wanted relative to the
chart or the x-axis categories. However, when a print
preview is done of the worksheet that includes the chart
(or even a print preview of the chart in many cases), or
if the worksheet is printed, the text box changes its
relative horizontal position to the chart. That is, what
is seen on the screen is not what you get in print preview
or in the print-out. This is especially true of text
boxes with many spaces between the text. No formating of
the text box seems to work, such as "size to chart," etc.
Thanks for any help!!

 

[Jon Peltier]

Chuck -

Textboxes can be incorporated into charts, but they don't really like to stay where they're put.
Are the textboxes part of the chart? If you select the chart, do the textboxes disappear? Can
you drag the textbox off the chart? Then they are not part of the chart. Select the chart prior
to using a drawing tool, to make sure the drawn objects are in the chart. This will improve
reliability marginally.

If you can use a data label, you will have more success. Create a dummy series, using X and Y
values to locate the points where you want the labels. Double click the new series, select none
for marker and line on the Patterns tab, then click on the Data Labels tab. Choose any single
item to show, and press okay. Then select the data label and change its text.

If you have a non-XY Scatter chart, you'll have to add the new series, change it to an XY
Scatter type (select just the series, go to Chart menu > Chart Type), then adjust the X and Y
values on whatever secondary axes may have been added by this change.

If you have a lot of labels, download a free third party Excel add-in to allow you to make lots
of labels all at once, based on labels in worksheet cells. Two such utilities are:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

I said before "If you can use a data label...". The user has almost complete control over a text

box, including its size and shape. A data label, on the other hand, has line breaks wherever it
wants, and all you can do is insert carriage returns to make it break lines sooner. You can't
undo a line break installed by Excel.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Chuck]

Jon --
Thanks very much for your time and helpful reply. I
hadn't thought of your suggestion, so I will give that a
try. It seems that your suggestion will work well for one
or two static charts. However, I have the charts and the
text box update automatically in using formulas and such.
Anyway, your suggestion is worth playing around with.
Thanks also for the recommended utilities. I will check
into those as well.

All the best,
Chuck

-----Original Message-----
Chuck -

Textboxes can be incorporated into charts, but they don't

really like to stay where they're put.

Are the textboxes part of the chart? If you select the

chart, do the textboxes disappear? Can

you drag the textbox off the chart? Then they are not

part of the chart. Select the chart prior

to using a drawing tool, to make sure the drawn objects

are in the chart. This will improve

reliability marginally.

If you can use a data label, you will have more success.

Create a dummy series, using X and Y

values to locate the points where you want the labels.

Double click the new series, select none

for marker and line on the Patterns tab, then click on

the Data Labels tab. Choose any single

item to show, and press okay. Then select the data label and change its text.

If you have a non-XY Scatter chart, you'll have to add

the new series, change it to an XY

Scatter type (select just the series, go to Chart menu >

Chart Type), then adjust the X and Y

values on whatever secondary axes may have been added by this change.

If you have a lot of labels, download a free third party

Excel add-in to allow you to make lots

of labels all at once, based on labels in worksheet cells. Two such utilities are:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

I said before "If you can use a data label...". The user

has almost complete control over a text

box, including its size and shape. A data label, on the

other hand, has line breaks wherever it

wants, and all you can do is insert carriage returns to

make it break lines sooner. You can't

 

[Jon Peltier]

Chuck -

Using Rob's or John's add-ins allow the datalabels to link to cells, just as textboxes do (or
you can do it manually). The only advantage of textboxes is that they can be reliably sized.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______