Looking for ideas: Userform Initializing Textbox arrangements

P

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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
MickG said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Populate userform dynamically with custom class Frame
Replies
1
Views
370
saboh12617
S
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
341
Micron
M
Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
174
Domenic
D
P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
245
Micron
M
F
  • Question Question
Returning changed dynamically created textbox value to corresponding cell
Replies
2
Views
550
frateg8r
F

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

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