View Single Post
  #1   Report Post  
DrLostinExcel
 
Posts: n/a
Default Change the size of the data labels

BAHHHhhhh.......

Thanks anyway Jon,

I am entirely too buried under 3 major contracts to play around with this
anymore. For the time being I'll just use PDFs that I CAN create without a
whole lot of hoop-jumping. I'll have to format a template set that will do a
standard 800X600 browser window but at least I won't have to play with this
Excel issue as well as some other PowerPoint workarounds I need to do.
One of my consultants (UNIX guy) suggested I take a look at Star Office
which is supposed to be able to read and write .ppt files and most other MS
formats. In addition it runs on just about every platform and costs about $40.
Probably not good talking about other software here but I can't tell you the
huge amount of time I've wasted trying to get this process automated....

THX again,

dlie


"Jon Peltier" wrote:

Doc -

Yes, this is certainly frustrating. And it means more work.

Using VBA you can measure the position of each data label (its .Top and .Left). It's
a little trickier, but you can also determine the position of the center of each
data label (not just algebra, because Excel won't tell you how tall or wide it is).
If interested, post back and I'll describe how to do this.

Then you can do a few nested loops (pseudocode):

For each ws in activeworkbook.worksheets
For each chtob in ws.chartobjects
for each srs in chtob.chart
For each dl in srs.datalabels
' code to get centers of dl
' dlHcenter, dlVcenter
set txt = activechart.Shapes _
.AddTextbox(msoTextOrientationHorizontal, _
100, 100, 200, 50)
' dummy temp dimensions (points)
with txt
.AutoSize = True
.TextFrame.Characters.Text = _
dl.text
.left = dlhcenter - .width/2
.top = dlvcenter - .height/2
end with
next ' dl
.datalabels.delete
next srs
next chtob
next ws

So it's a pain, but not insurmountable. Easier than searching for another program
and learning how to make it do more than it's user interface can handle without
special programming.

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

DrLostinExcel wrote:

WOW....
This is VERY VERY VERY BAD!!!!!!!!!!!!!!!!!!!!
I have HUGE presentations that I'm trying to generate automatically using
PowerPoint and Excel. I have between 40 and 90 graphs per presentation and
the number of items changes so how can I possibly know where to put the
labels?
What an INCREDIBLE lack of foresight...............
Time to look for an alternative to PowerPoint/Excel
Thanks Jon

"Jon Peltier" wrote:


Unfortunately, Excel does not trust its users to resize data labels, chart titles,
or axis titles. If you need this level of control, you need to replace the data
labels with text boxes.

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

DrLostinExcel wrote:


How do I change the size of a data label in a pie chart? I am getting
unusable wrapping of text when I have plently of room if I could only resize
the label box. All I've been able to do is move the label.

Thanks,

DLIE