It's hard to work out what you are actually doing, in which version and if
in 2007 if that's with an xls format file. Clearly it seems not all the
shapes you think are textboxes are in fact textboxes. In 2003 attempting to
return the text would error (though not in 2007). Try something like this -

Dim shp As Shape
On Error Resume Next
For Each shp In ActiveSheet.Shapes
s = ""
s = shp.DrawingObject.Text
Debug.Print shp.Name, IIf(Len(s), s, "no-text")
on error goto 0 ' or resume normal error handling
Next

Regards,
Peter T


"Estaup" wrote in message
...
Here are the results which are interesting:

The sheet in question has two TextBoxes.

TextBox 1 contains static text that is added during the generation
process.
The debug lists the TextBox name correctly and the contents with the TB
for
loop.

TextBox 2 does not show up in the TB for loop, but does show up in the
Shape
for loop (P.S. I had to change the code you posted to be
ActiveSheet.Shapes
for the shape loop) as does TextBox 1. Both boxes has the type of 17.
TextBox
2 has no content.

If I add text to TextBox 2 then save & close the workbook. If I process
the
workbook again after text having been entered, then TextBox 2 show up in
both
the TB and the SHAPE for loops as expected.

hmmmmm.



"Peter T" wrote:

How are you adding the textboxes to the sheet, I take it not in the way
you
described originally.

Referring to your OP
"If I go to each of the sheets, and click to Add text in each"

The "Add Text" button does not appear on a textbox, only Edit Text. This
rather suggests the object named "TextBox 1" is not a textbox at all. Try
these -

Dim tbx as Textbox
For each tbx in activesheet.textboxes
debug.print tbx.name, tbx.text
next

dim shp as shape
for each shp in activesheet.textboxes
debug.print shp.name, shp.type
next


You should expect to see type 17 (msotextbox)

Regards,
Peter T




"Estaup" wrote in message
...
I probably need to clarify too, that the code works just fine in 2007.
These
problems are occuring when the code is run in Excel 2003.

So all results and testing being posted here is all being done in Excel
2003.

"Estaup" wrote:

Sorry for the delay in response. I got pulled to another project right
after
posting this and I am just now getting back.

The worksheet is being created in 2007 but then saved as 97-2003 XLS
file
format because most of our customers are still on Office 03.



"Peter T" wrote:

Which version of Excel are you using. You say "Textboxes from the
drawing
tool" which implies 97-2003, however the default names you show
imply
inserted in 2007
(ie "TextBox 2" rather than "Text Box 2")

Regards,
Peter T

"Estaup" wrote in message
...
I have posted elsewhere to try and get resolution on my issue and
decided
to
ask for help here as well.

I have an issue that is driving me crazy. I have a workbook that
has
multiple sheets and some of these sheets have Textboxes (from the
drawing
tool) on the sheet with standard naming (i.e. TextBox 1, TextBox
2,
etc.)

I have a button that executes code that takes and builds an
external
file
with the data from the worksheets, including the TextBoxes. During
that
process I am checking to see if those worksheets with TextBoxes
have
anything
entered before attempting to grab the contents and write them to
the
external
file.

Here is the crazy part. If I open the workbook and without doing
anything
else, click the button to process I get the 'Unable to get
TextBoxes
property
of Worksheet Class' error message.

If I go to each of the sheets, and click to Add text in each of
them,
then
click the button to process I do not get the error and everything
is
fine.

Going nuts on this one.

The IF statement below is where the code breaks with the error:

If Len(Worksheets(2).TextBoxes("TextBox 2").Text) 0 Then
'I loop through grabing text out 255 characters at a time for
processing
purposes
End If

Any help would be appreciated.

Thanks.


.



.