Excel 2007 Text Box

[Bill]

Hello,
I am trying to add a text box to the bottom left portion of a chart using
Excel 2007 (the trial version downloaded from Microsoft). I could do this
with Excel 2003 using the following code:

Chheight = ActiveChart.ChartArea.Height
Chtleft = ActiveChart.ChartArea.Left
ActiveChart.TextBoxes.Add(Chtleft, Chheight, 10, 10).Select
Selection.Characters.Text = "This is my text"
Selection.autosize = true

With Excel 2007, it will not place the text box below the x axis, no matter
what value I put in for Chheight. And, I get an error message when trying
to autosize the text box.

Any ideas? Thanks,

Bill

 

[Nick Hodge]

Bill

I believe that was broken in all beta releases, it is now fixed on RTM

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Jon Peltier]

Well, it doesn't look fixed to me. I ran this short procedure on an embedded
chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
0, ActiveChart.ChartArea.Height, 100, 12)

.TextFrame.Characters.Text = "Testing text box position"

End With

End Sub

and the resulting textbox, which in 2003 would have butted against the
bottom of the chart, instead extended below the chart. It was line wrapped
like this

Testing text box
position

with the top 2/3 of the first line in the chart, and the rest showing below
it.

When I ran the same procedure on a chart sheet, the textbox was created
above the lower axis. Inserting this line before End With did not move the
textbox:

.Top = ActiveChart.ChartArea.Height

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

 

[Jon Peltier]

The textbox within the embedded chart works as expected if the textbox does
not line wrap.

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

 

[Bill]

Hello,
It does work on the embedded chart, but not on a chart that has been moved
to a new sheet. I can't seem to place the text box below the x axis in this
case.

Bill

 

[Jon Peltier]

Right, I can do it manually (with difficulty because it isn't easy to select
the textbox, especially with the touchpad on that old laptop), but VBA can't
seem to access the bottom region of the chart. I tried various combinations
of

.Top = ActiveChart.ChartArea.Height

thinking that maybe order of commands had something to do with it. Nope. I
still have one or two ideas, but I'm not holding out much hope. I think the
textboxes are not yet fixed.

- Jon

 

[Jon Peltier]

Okay, my lame idea worked. This will move the textbox to the bottom of the
chart:

ActiveChart.Shapes(1).IncrementTop ActiveChart.ChartArea.Height -
ActiveChart.Shapes(1).Top

so the working procedure is:

Sub ChartTextBox2()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
0, 0, 200, 16)

.TextFrame.Characters.Text = "Testing text box positioning"
.IncrementTop ActiveChart.ChartArea.Height - .Top

End With

End Sub

I also noticed the other day while playing around with textboxes that
autosizing a new textbox in Excel 2007 does not change the width of the
textbox to fit the text, only the height. So unless I've missed something
obvious (which is not completely unlikely), a large number of simple
routines will now not work as expected.

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

 

[Jon Peltier]

If I create the text box at 0,0

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
0, 0, 200, 16)

There is still a margin between the chart's edges and the textbox's. I can
drag the textbox up into the corner, and now its .left and .top properties
both read -4. This isn't just the textbox, either. I can drag the plot area
into the top left corner, and its .left and .top also both read -4.

We can work with all of this, but it's sure inconsistent.

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

 

[Jon Peltier]

Other shapes behave the same as the text box, in that they have problems
going low on a chart sheet, but they go where you put them on an embedded
chart, with text spilling out of the chart over the worksheet.

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

 

[Jon Peltier]

and the resulting textbox, which in 2003 would have butted against the

Hmm, I spoke without checking. In 2003, the textbox will position itself so
its top edge is at the bottom of the chartarea, and the rest of it is not
seen, but it's in a hidden drawing layer in the chart just out of view.
After it is dragged back into the chart area, it can no longer be moved
outside of the chart area.

I'd never noticed that a shape could be drawn so its initial position is
outside the bounds of the chart area. Learn something new every day.

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

 

[newsgroups]

This is indeed a problem, I have it as well. From the Excel 2007 VBA
documentation, this behavious is as expected because the object
created is considered to be a multiline text object and the height is
changed by Autosize and not the width (as per all previous versions of
Excel).

What I cant find is how to chnage this to a non-multiline text object.
The documentation referes to a property Multiline = boolean but this
errors as an undefined property.

Any one can help it would be greaty appreaciated bu both of us.

Cheers,
Dennis.