Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recognizing textbox within a grouped shape. 1

Status
Not open for further replies.

FractalWalk

Technical User
Nov 18, 2002
141
US
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?
 
FractalWalk,
Will this work in Excel '02?
Code:
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)
 
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?
 
Is tb declared as a Control? An Object?

Can you use something like:
Code:
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
 




or this...
Code:
ActiveSheet.Shapes(tb.Name).oleformat.object.BackStyle = 0


Skip,

[glasses] [red][/red]
[tongue]
 
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.
 
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?
 



Code:
    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,

[glasses] [red][/red]
[tongue]
 
AWESOME!!!!!!!!!!!!!

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




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

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

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top