Looking for ideas: Userform Initializing Textbox arrangements

[Peter h]

I've designed a userform using Frames. I'm new to frames and trying to learn how to utilize them. Right now I've got 4 frames (the number of frames will be 11 when I'm done building the userform), and each one of them has a different number of textboxes in them. In the UserForm1.Initialize sub, I'm trying to arrange all of my textboxes. Right now I've got it set up to set the top and left properties of each textbox individually, but thought I might be able to accomplish this using a loop. What I'm not sure of is how to get it to go to the next frame once it has lined up the textboxes in the first frame. Does that make sense?

Here's a bit of code I made that works to line them up the way I want, but because it doesn't reference the frames, then after the first frame the textboxes top property is not right.

[FONT=Calibri][SIZE=3][COLOR=#000000]Private SubUserForm_Initialize()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim fCont AsControl[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim txtCountAs Integer[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]txtCount = 0[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
For Each fCont In Me.Controls[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   If TypeName(fCont) = "TextBox" Then txtCount = txtCount + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next fCont[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'LINE UP ALLTEXTBOXES[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For i = 5 To txtCount[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   With Me.Controls("TextBox" & i)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Width = 40[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Top = Me.Controls("TextBox" & i - 2).Top + Me.Controls("Textbox" & i - 2).Height + 10[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Left = Me.TextBox3.Left[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       Me.Controls("TextBox" & i) = ""[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       i = i + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]For i = 6 To txtCount[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   With Me.Controls("TextBox" & i)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Width = 40[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Top = Me.Controls("TextBox" & i - 2).Top + Me.Controls("Textbox" & i - 2).Height + 10[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       .Left = Me.TextBox4.Left[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       Me.Controls("TextBox" & i) = ""[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]       I = i + 1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]

 

[MickG]

You could try the below modified to your requirements.

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] ctrl [COLOR="Navy"]As[/COLOR] Control, top
[COLOR="Navy"]Dim[/COLOR] ctr [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ctrl [COLOR="Navy"]In[/COLOR] UserForm1.Controls
    top = 10
    [COLOR="Navy"]If[/COLOR] TypeOf ctrl [COLOR="Navy"]Is[/COLOR] msforms.Frame [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ctr [COLOR="Navy"]In[/COLOR] ctrl.Controls
            [COLOR="Navy"]If[/COLOR] TypeOf ctr [COLOR="Navy"]Is[/COLOR] msforms.TextBox [COLOR="Navy"]Then[/COLOR]
                ctr.Left = 15
                ctr.top = top
                 top = top + 15 + ctr.Height
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter h]

You could try the below modified to your requirements.

Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] ctrl [COLOR=navy]As[/COLOR] Control, top
[COLOR=navy]Dim[/COLOR] ctr [COLOR=navy]As[/COLOR] Control
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] ctrl [COLOR=navy]In[/COLOR] UserForm1.Controls
    top = 10
    [COLOR=navy]If[/COLOR] TypeOf ctrl [COLOR=navy]Is[/COLOR] msforms.Frame [COLOR=navy]Then[/COLOR]
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] ctr [COLOR=navy]In[/COLOR] ctrl.Controls
            [COLOR=navy]If[/COLOR] TypeOf ctr [COLOR=navy]Is[/COLOR] msforms.TextBox [COLOR=navy]Then[/COLOR]
                ctr.Left = 15
                ctr.top = top
                 top = top + 15 + ctr.Height
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR]
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you. That is a big help. The only thing I'm thinking is that right now I have 2 columns of textboxes, and yours lines them all up into 1 column. The way I have it setup is that Textbox3 and Textbox4 are in a set location, so I was setting the other textboxes left attribute to those. So in each frame I'll have odd numbered textboxes in the left column and even numbered ones in the second column. So like:
Textbox1 Textbox2
Textbox3 Textbox4
Textbox5 Textbox6 etc, etc...

That's why my code had 2 loops.

Another issue is that each frame could have a different amount of textboxes in it. So like Frame1 could have 7 textboxes, and frame 2 could have 4. So, this throws my loop out because if Frame 1 has 7 textboxes, then the first textbox in frame 2 will be an even number, but I want it to reset to the top left of the frame. Make sense?

 

[Peter h]

I was able to use your code to figure out how to do it. Here's the code I came up thanks to your help. It seems to be working perfectly.

Private Sub UserForm_Initialize()
Dim fCont As Control
Dim fCTRL As Control
Dim leftC As Boolean
'LINE UP ALL TEXTBOXES
For Each fCont In Me.Controls
    If TypeOf fCont Is MSForms.Frame Then
        leftC = True
        i = Me.TextBox3.Top
        For Each fCTRL In fCont.Controls
            If TypeOf fCTRL Is MSForms.TextBox Then
                If leftC = True Then
                    fCTRL.Width = 40
                    fCTRL.Top = i
                    fCTRL.Left = Me.TextBox3.Left
                    fCTRL = ""
                    leftC = False
                Else
                    fCTRL.Width = 40
                    fCTRL.Top = i
                    fCTRL.Left = Me.TextBox4.Left
                    fCTRL = ""
                    leftC = True
                    i = i + fCTRL.Height + 10
                End If
            End If
        Next fCTRL
    End If
Next fCont