Unable to get TextBoxes property of Worksheet Class Error

[Estaup]

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.

 

[JLGWhiz]

This might work:

 

[JLGWhiz]

Try it again, I clicked the wron thing.


If Len(Sheets(2).OLEObjects("TextBox1").Object.Text) > 0 Then
MsgBox "OK"
End If

TextBoxes is not in the Object Model

 

[Peter T]

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

 

[Skiffle]

Estaup,

So you don't make a simple syntax error that is difficult to find; the
number of a control is specified immediately after its name with no space
between them.
..
For textboxes, the names are TextBox1, TextBox2; not TextBox 1, TextBox 2.
Notice, in the correct form, there is no space between 'TextBox' and the
number following it.

For the correct syntax, refer to JLGWhiz' second post.

Cheers,

Skiffle

 

[Peter T]

A Name like "TextBox1" infers the default name for an ActiveX type Texbox.
The OP has already said they are not aX but from "the drawing tool".
Therefore the default naming style will be "TextBox 1" or "Text Box 1"
depending on the Excel version.

However there is something ambiguous in the OP's description which was the
point of my post.

Regards,
Peter T

 

[Estaup]

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.

 

[Estaup]

Just tried this and I get the same error message on the IF statement line.

I have tried doing the following and got an interesting result:

For Each tb in worksheets(2).TextBoxes
MsgBox(tb.name)
Next tb

I went through the loop and other textboxes that are on the page showed up
in the list but not TextBox 2. In this particular case, TextBox 2 does not
have any text currently entered into its contents.

If I then right-click on the textbox, choose Add Text, then execute the
above code I will get a MsgBox with the TextBox 2 name being displayed.

 

[Estaup]

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]

I also checked the TB object at that time in the Watch window and verified
that the Parent of the TextBox 2 object is truely sheet 2 and not something
else.

 

[Peter T]

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

 

[Skiffle]

Peter,

An oversight on my part. I do stand corrected.

Cheers,

Skiffle

 

[Estaup]

I have VBA script that is generating the workbook based on parameters for
each worksheet. When the parameter specifies for a TextBox to be on the sheet
one is being generated using the ActiveSheet.TextBoxes.Add method.

Now the generating VBA code was originally writen for Office 03. Subtle
changes were need when Office was upgraded to 07. So in a since the generated
XLS file with the TextBoxes and VBA code is being produced by 07 I'm guessing
running in "compatibility mode"??

I will run the suggested code and post the results.

 

[Estaup]

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]

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