Looking for ideas: Userform Initializing Textbox arrangements

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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.

Code:
[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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You could try the below modified to your requirements.
Code:
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
 
Upvote 0
You could try the below modified to your requirements.
Code:
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?
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top