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

Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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" wrote in message
...
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/
_______

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bill" wrote in message
ink.net...
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" wrote in message
...
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/
_______

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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(msoTextOrientationHo rizontal, _
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" wrote in message
...
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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bill" wrote in message
ink.net...
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" wrote in message
...
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/
_______

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

If I create the text box at 0,0

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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" wrote in message
...
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(msoTextOrientationHo rizontal, _
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" wrote in message
...
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, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Bill" wrote in message
ink.net...
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" wrote in message
...
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/
_______

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in
message ...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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

"Jon Peltier" wrote in message
...
Well, it doesn't look fixed to me. I ran this short procedure on an
embedded chart:

Sub ChartTextBox()

With ActiveChart.Shapes.AddTextbox(msoTextOrientationHo rizontal, _
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/
_______

"Nick Hodge" wrote in message
...
Bill

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Bill" wrote in message
ink.net...
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

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.

On Dec 6 2006, 3:51 am, "Bill" wrote:
Hello,
I am trying to add a text box to the bottom left portion of a chart usingExcel2007 (the trial version downloaded from Microsoft). I could do this
withExcel2003 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

WithExcel2007, 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
toautosizethe text box.

Any ideas? Thanks,

Bill