Recognizing textbox within a grouped shape. 1

[FractalWalk]

I am using Excel 2002 and have a series of shapes grouped together. Half of them are Form Control TextBoxes and half of them are freeform shapes.

I want to be able to identify the textboxes and change the properties using the mousemove event but I am having problems. Here is the code I am using:

For Each Sh In ActiveSheet.Shapes
If Sh.Type = 6 Then 'Group object
For Each tb In Sh.GroupItems
If tb.Type = 12 Then 'textbox object
tb.BackStyle = 0
End If
Next tb
Exit For
End If
Next

This code identifes each textbox but it won't allow me to use the BackStyle property against it. When I reference the individual TextBoxes by name it works. For example:

TextBox1.BackStyle = 0

That works just fine. But when I identify the object using .GroupItems it doesn't work. I need to identify each TextBox object on the fly within a loop. Any ideas?

 

[CautionMP]

FractalWalk,
Will this work in Excel '02?

For Each Sh In ActiveSheet.Shapes
    If Sh.Type = 6 Then    'Group object
        For Each tb In Sh.GroupItems
            If tb.Type = 12 Then   'textbox object
                ActiveSheet.Shapes(tb.Name).BackStyle = 0   
            End If
        Next tb
        Exit For
    End If
Next

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[FractalWalk]

No, that didn't work.

I think when I call it that way it is viewing the textbox as a shape as opposed to a control. I can use all of the properties that a shape can have but none that a control has. But if I reference it by the object name I have all the control properties.

Is there some way to tell it that it is a form control or is there some sort of form control array I could loop through?

 

[fumei]

Is tb declared as a Control? An Object?

Can you use something like:

For Each Sh In ActiveSheet.Shapes
  If Sh.Type = 6 Then    'Group object
    For Each tb In Sh.GroupItems
      If TypeOf tb Is MSForms "Textbox" Then
         ActiveSheet.Controls(tb.Name).BackStyle = 0   
      End If
    Next tb
    Exit For
  End If
Next

Gerry
My paintings and sculpture

 

[SkipVought]

or this...

ActiveSheet.Shapes(tb.Name).oleformat.object.BackStyle = 0

Skip,

_[red][/red]

 

[FractalWalk]

Well, that didn't work but it is narrowing down the issue.

When I ran the OleFormat.object line, I get an error of "The item with the specified name wasn't found."

When I ran the TypeOf code, it didn't even recognize the object as a forms textbox.

When I ungroup all of the shapes and search for a textbox shape it doesn't find any, but when I group them all together, it finds these as textbox shapes.

These textboxes are not on forms but embedded in a sheet. That seems to cause them not to be recognized as form controls even though they have all the properties of one. And when I group them with shapes, Excel thinks it is a textbox shape and only let's me find it as a shape and perform shape properties on it. But when I identify it by name (not as a shape) it let's me do control properties on it.

So, I can't find it in the shape array (except as a shape). There is no controls array on the sheet (that I know of). So I am lost as to how I can find this object on the sheet.

 

[FractalWalk]

Does anyone know how I can find embedded controls on a sheet when it isn't on a form? Is there some object array or something like that I can use?

 

[SkipVought]

    Dim sh As Shape, tb As Object
    
    For Each sh In ActiveSheet.Shapes
        If sh.Type = 6 Then    'Group object
            For Each tb In sh.GroupItems
                If tb.Type = 12 Then   'textbox object
                    tb.OLEFormat.Object.Object.BackStyle = fmBackStyleTransparent
                End If
            Next tb
            Exit For
        End If
    Next

Skip,

_[red][/red]

 

[FractalWalk]

AWESOME!!!!!!!!!!!!!

That works perfectly. You have saved my life. Thanks so much and here's a star

 

[SkipVought]

BTW, I used this technique to discover the available properties...

How to use the Watch Window as a Power Programming Tool faq707-4594

Skip,

_[red][/red]