How to get Macro to loop through multiple frames

[ilya2004]

Hello,

I have a macro that creates a background image behind every textbox in a userform. The textbox is transparent so the image becomes like the background for the textbox.

the macro esentially goes like this:

    Dim box As Control
    For Each box In Me.Controls
        'Code to create back image
    Next

Now I moved some of the boxes onto different frames.

I know I could do:

For Each box in Me.Frame1.Controls

but I wanted to know if there was a way I could get it to loop through all of the frames and do it automatically.

Thanks!
-Ilya

 

[mikerickson]

Looping through each control in the Userform's controls collection should catch all the controls in each frame.

Dim oneControl as Object

For each oneControl in Userform1.Controls
    If TypeName(oneControl) = "TextBox" Then
        MsgBox oneControl.Name & " is in " & oneControl.Parent.Name
    End If
Next oneControl

 

[ilya2004]

Hi Mike,

Thanks for your help, I was able to realize what my error was. It wasn't that Excel did not identify the boxes, but the background image that I was trying to create was being planted in the module itself, and not in the frame behind the textbox where it should have. How do I get it to dynaimcally create the image in the frame. Here is my whole code:

Sub BoxBG()
Dim cbBG As Control
Dim obBox As Control
 
For Each obBox In frmMain.Controls
If obBox.Name Like "i*" Then
Set cbBG = frmMain.Controls.Add("Forms.image.1")
With cbBG
.Visible = True
.Picture = frmIndv2.obBGsource.Picture
.PictureAlignment = 2
.PictureSizeMode = 1
.Width = obBox.Width
.Height = obBox.Height
.Left = obBox.Left
.Top = obBox.Top
.Name = obBox.Name & "bg"
.ZOrder 1
.BorderStyle = 0
End With
End If
Next
End Sub

 

[ilya2004]

I should also clarify that the controls are in a multi-page now, not in a frame as they were before

 

[mikerickson]

Instead of adding to frmMain's Control collection, add to the obBox's container's Controls Collection.

Set cbBG = obBox.Parent.Controls.Add("Forms.image.1")

 

[ilya2004]

that's what brings me back to the problem I had to begin with, I have 7 or 8 different parents for all the controls, so i want to automatically generate them in each parent.

 

[mikerickson]

Each control has only one parent.
If you tried the test routine in post#2, you'll see that the .Parent of a control is the bottom level container of the control.
i.e. if myForm contains MultiPage1, Page(0) of which has Frame8 which contains TextBox8, then
TextBox8.Parent is Frame8
TextBox8.Parent.Parent is Page0
TextBox8.Parent.Parent.Parent is Multipage1
TextBox8.Parent.Parent.Parent.Parent is myForm
TextBox8.Parent.Parent.Parent.Parent.Parent errors.

The point of which is that as you loop through Each oneControl in Userform1.Controls, the .Parent property of oneControl will get all the containers.

 

[ilya2004]

I realize that I wasn't clear before, I figured out how to get it to do what I wanted. I simply preceded the code I posted above with the following:

Dim pg As Control
For Each pg In frmMain.mpMain.Pages