Can you tweak these codes for Userform : Make it small and efficient.

A

Akanjana

Board Regular
Joined
Mar 22, 2020
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
I want to add dynamic userform controls like (labels, textboxs) on runtime, when userfrom activated with following order.

i want something like following

when userfrom activate, it needs to ask user number of fields, he /she want to insert ?
if user answer 7, then it need to add field in following order "3 columns order "

Label1 textbox1 Label2 textbox1 Label3 textbox3

Label4 textbox4 Label5 textbox5 Label6 textbox6

Label7 textbox7 ........and so on based on number of field user ask.


i tried following codes:


VBA Code: 
Private Sub UserForm_Initialize()

Dim i As Long

number = 10 'InputBox("Enter no of text-boxes and labels you wish to create at run-time", "Enter TextBox & Label Number")

Dim txtB1 As control

For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With

Next i


For i = 6 To 10

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 200
.Top = 20 * i - 100 * 1
End With

Next i


Dim lblL1 As control

For i = 1 To 5

Set lblL1 = Controls.Add("Forms.Label.1")

With lblL1
.Caption = "Label" & i
.Name = "lbl" & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With

Next i


For i = 6 To 10

Set lblL1 = Controls.Add("Forms.Label.1")

With lblL1
.Caption = "Label" & i
.Name = "lbl" & i
.Height = 20
.Width = 50
.Left = 150
.Top = 20 * i - 100 * 1
End With

Next i


Dim q As Long

For q = 1 To 5

Controls("lbl" & q) = Cells(1, q)

Next q


For q = 6 To 10

Controls("lbl" & q) = Cells(1, q)

Next q

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
.
This is most of it (one method) :

VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

Dim i As Long
Dim Number As Integer

Number = 10 'InputBox("Enter no of text-boxes and labels you wish to create at run-time", "Enter TextBox & Label Number")

Dim txtB1 As Control

For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With

Next i


For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 200
.Top = 20 * i * 1
End With

Next i

Dim theLabel As Object
Dim labelCounter As Long
labelCounter = 1
For i = 1 To 5
  
        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 20
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
            
        End With
    labelCounter = labelCounter + 1

Next

labelCounter = 6
For i = 1 To 5

        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 150
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
            
        End With
        
labelCounter = labelCounter + 1
Next

End Sub

Not certain what you are attempting to do with the last part of your original macro .... "q" ... ??
 
Upvote 0
Logit said:
.
This is most of it (one method) :

VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

Dim i As Long
Dim Number As Integer

Number = 10 'InputBox("Enter no of text-boxes and labels you wish to create at run-time", "Enter TextBox & Label Number")

Dim txtB1 As Control

For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With

Next i


For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 200
.Top = 20 * i * 1
End With

Next i

Dim theLabel As Object
Dim labelCounter As Long
labelCounter = 1
For i = 1 To 5
 
        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 20
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
           
        End With
    labelCounter = labelCounter + 1

Next

labelCounter = 6
For i = 1 To 5

        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 150
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
           
        End With
       
labelCounter = labelCounter + 1
Next

End Sub

Not certain what you are attempting to do with the last part of your original macro .... "q" ... ??
Click to expand...


"q" macro just change the label of worksheet column heading, when form label name is changed. sorry i forgot to remove it. its extra code.

regarding my first code.

The issue is that i have to run almost 4 for loops to achieve the desired results. can't it be done with 1 or 2 nested for loops ? .also if you observe the codes, its like presently my loop to limited to 10 numbers only. can i achieve it in 3 columns and unlimited rows based on no of fields user needs to create.

Can you please give me code from your end to add 21 textbox control in 3 columns order as above.
like

in following order:

textbox 1 textbo2 textbox3
textbox 4 textbo5 textbox6
textbox 6 textbo7 textbox8
textbox 9 textbo10 textbox11
textbox12 textbo13 textbox14
textbox15 textbox16 textbox17
textbox18 textbo19 textbox20
textbox21

what will be you code to achieve above results ?
 
Upvote 0
Logit said:
.
This is most of it (one method) :

VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

Dim i As Long
Dim Number As Integer

Number = 10 'InputBox("Enter no of text-boxes and labels you wish to create at run-time", "Enter TextBox & Label Number")

Dim txtB1 As Control

For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With

Next i


For i = 1 To 5

Set txtB1 = Controls.Add("Forms.TextBox.1")

With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 50
.Left = 200
.Top = 20 * i * 1
End With

Next i

Dim theLabel As Object
Dim labelCounter As Long
labelCounter = 1
For i = 1 To 5

        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 20
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
         
        End With
    labelCounter = labelCounter + 1

Next

labelCounter = 6
For i = 1 To 5

        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 150
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
         
        End With
     
labelCounter = labelCounter + 1
Next

End Sub

Not certain what you are attempting to do with the last part of your original macro .... "q" ... ??
Click to expand...


@Logit Just tested your modified code, it add control in 2 columns, can your make it in order of 3 column like following based on combox value or inputbox value

A B C
D E F
G H I

and so on ?
 
Upvote 0
.
This is as short as I can make. I know there are other methods with less code but someone else will need to assist you with that.

VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

Dim i As Long
Dim txtB1, txtB2, txtB3 As Control
Dim theLabel, thelabel1, thelabel2 As Object
Dim labelCounter As Long
labelCounter = 1

For i = 1 To 7

Set txtB1 = Controls.Add("Forms.TextBox.1")
Set txtB2 = Controls.Add("Forms.TextBox.1")
Set txtB3 = Controls.Add("Forms.TextBox.1")

    With txtB1
    .Name = "txtBox" & i
    .Height = 20
    .Width = 50
    .Left = 70
    .Top = 20 * i * 1
    
        With txtB2
        .Name = "txtBox" & i
        .Height = 20
        .Width = 50
        .Left = 200
        .Top = 20 * i * 1
        End With
        
        With txtB3
        .Name = "txtBox" & i
        .Height = 20
        .Width = 50
        .Left = 330
        .Top = 20 * i * 1
        End With
        
    End With

Next i

For i = 1 To 7
  
        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        Set thelabel1 = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        Set thelabel2 = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 20
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
        End With
            
            With thelabel1
                .Height = 20
                .Width = 50
                .Left = 150
                .Top = 20 * i * 1
                .Caption = "Test" & labelCounter
            End With
        
            With thelabel2
                .Height = 20
                .Width = 50
                .Left = 280
                .Top = 20 * i * 1
                .Caption = "Test" & labelCounter
                
            End With
        
    labelCounter = labelCounter + 1

Next

End Sub
 
Upvote 0
Logit said:
.
This is as short as I can make. I know there are other methods with less code but someone else will need to assist you with that.

VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

Dim i As Long
Dim txtB1, txtB2, txtB3 As Control
Dim theLabel, thelabel1, thelabel2 As Object
Dim labelCounter As Long
labelCounter = 1

For i = 1 To 7

Set txtB1 = Controls.Add("Forms.TextBox.1")
Set txtB2 = Controls.Add("Forms.TextBox.1")
Set txtB3 = Controls.Add("Forms.TextBox.1")

    With txtB1
    .Name = "txtBox" & i
    .Height = 20
    .Width = 50
    .Left = 70
    .Top = 20 * i * 1
   
        With txtB2
        .Name = "txtBox" & i
        .Height = 20
        .Width = 50
        .Left = 200
        .Top = 20 * i * 1
        End With
       
        With txtB3
        .Name = "txtBox" & i
        .Height = 20
        .Width = 50
        .Left = 330
        .Top = 20 * i * 1
        End With
       
    End With

Next i

For i = 1 To 7
 
        Set theLabel = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        Set thelabel1 = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
        Set thelabel2 = UserForm1.Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
       
        With theLabel
            .Height = 20
            .Width = 50
            .Left = 20
            .Top = 20 * i * 1
            .Caption = "Test" & labelCounter
        End With
           
            With thelabel1
                .Height = 20
                .Width = 50
                .Left = 150
                .Top = 20 * i * 1
                .Caption = "Test" & labelCounter
            End With
       
            With thelabel2
                .Height = 20
                .Width = 50
                .Left = 280
                .Top = 20 * i * 1
                .Caption = "Test" & labelCounter
               
            End With
       
    labelCounter = labelCounter + 1

Next

End Sub
Click to expand...

@Logit Thanks for your code : can you please tweak code little bit


Your Above code insert Control in following manner
YourCode.PNG



I want is it to added in following manner



DesiredResult.PNG



Can you please provide code
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
239
NoSparks
N
L
  • Question
VBA: Dynamical Userform from Module
Replies
0
Views
636
lilcrisis
L
N
  • Question
How to use array values from userform as a variable in worksheet
Replies
1
Views
889
nsook
N
H
  • Question
VBA Project, Copy enabled labels and textboxes from a userform to another userform
Replies
0
Views
188
HosEsf
H
H
  • Solved
Reference to Dynamic Label and Textbox in userform
Replies
2
Views
3K
hajiali
H

Forum statistics

Threads
1,214,788
Messages
6,121,603
Members
449,038
Latest member
Arbind kumar

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