Gap between 65 textboxes and labels is huge. Require the perfect gap between eachother

N

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
688
Hello

What i want is to display 65 textboxes and 65 labels adjacent to each other in 3 or 5 columnar way on the userform at run time.

Textbox and Label Top Position starts from 144

so when used the following in below code for both controls textbox and label .Top = 144 * i * 1 the gap between each Label and Textbox is huge and looks bad.
How can i get the perfect gap between each label and Textbox respectively below each other and also perfect gap for columnar representation on userform.

Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
......so on


Code: 
Private Sub UserForm_Initialize()
Dim i As Integer
Dim txtbxTopPos As Integer
Dim txtbxGap As Integer


Dim txtB1 As Control
Dim labl As Control


For i = 1 To 65
Set txtB1 = Controls.Add("Forms.TextBox.1")
Set labl = Controls.Add("Forms.Label.1")
With txtB1
.Name = "txtBox" & i
.Height = 15.75 
.Width = 126    
.Left = 102     
.Top = 144 * i * 1
End With


With labl
.Name = "lbl" & i
.Height = 15.75 
.Width = 126    
.Left = 10     
.Top = 144 * i * 1
.BackStyle = 0
.Caption = Sheet1.Cells(1, i).Value
End With

Next i
End Sub

Thankx NimishK
 
Last edited:
Sort by date Sort by votes
yes i removed I
txtB1.Left = txtB1.Left + 25
labl.Left = labl.Left + 25

But still repesentation

Label TextBox
Label TextBox
Label TextBox
Label TextBox

and NOT

Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox
Label TextBox Label TextBox Label TextBox

How to achieve above.
 
Last edited:
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
can you upload the worksheet to dropbox ??
So I can play with it AND the code
 
Upvote 0
NimishK said:
here you go

https://www.dropbox.com/s/ohzzmc7qj8vf3hq/RunTimeUFTextBoxes.xlsm?dl=0
Click to expand...

Maybe this:

Code: 
Private Sub UserForm_Initialize()
Dim i As Integer
Dim txtbxTopPos As Integer
Dim txtbxGap As Integer


Dim txtB1 As Control
Dim labl As Control

y = 144
x = 10
For i = 1 To 65


    Set txtB1 = Controls.Add("Forms.TextBox.1")
    Set labl = Controls.Add("Forms.Label.1")
    
    If i = 23 Then
        x = 300
        y = 144
        ElseIf i = 46 Then
        x = 600
        y = 144
    End If
    
    With txtB1
        .Name = "txtBox" & i
        .Height = 15.75
        .Width = 116
        .Left = 102 + x
        .Top = y
    End With


    With labl
        .Name = "lbl" & i
        .Height = 15.75
        .Width = 126
        .Left = x
        .Top = y
        .BackStyle = 0
        .Caption = Sheet1.Cells(1, i).Value
    End With
    y = y + 25
Next i

End Sub
 
Upvote 0
Akuini This was a rock-solid one. Thankx:beerchug:
yes you are right you made me think on x and y co-ordinates which i never thought off.

And Added in Red
Dim y As Integer
Dim x As Integer
the above were not dimensioned

Code: 
Private Sub UserForm_Initialize()

Dim i As Integer
Dim txtbxTopPos As Integer
Dim txtbxGap As Integer

[COLOR=#ff0000]Dim y As Integer
Dim x As Integer[/COLOR]

Dim txtB1 As Control
Dim labl As Control

y = 144
x = 10
For i = 1 To [B][COLOR=#ff0000]68[/COLOR][/B]

    Set txtB1 = Controls.Add("Forms.TextBox.1")
    Set labl = Controls.Add("Forms.Label.1")
    
    If i = 23 Then
        x = 300
        y = 144
      ElseIf i = 45 Then
        x = 600
        y = 144
        
[COLOR=#ff0000]       ElseIf i = 67 Then[/COLOR]
[COLOR=#ff0000]        x = 900[/COLOR]
[COLOR=#ff0000]        y = 144[/COLOR]

[COLOR=#ff0000]    End If[/COLOR]

    With txtB1
        .Name = "txtBox" & i
        .Height = 15.75
        .Width = 116
        .Left = 102 + x
        .Top = y
        .Text = i
    End With

    With labl
        .Name = "lbl" & i
        .Height = 15.75
        .Width = 126
        .Left = x
        .Top = y
        .BackStyle = 0
        .Caption = Sheet1.Cells(1, i).Value
    End With
    y = y + 25
Next i
End Sub

Michael were you going to code like above or you had something different in mind
 
Last edited:
Upvote 0
If you want to make it look equal why not 66 textbox? so each column has 22 textbox

just use (in my original code):

Code: 
For i = 1 To 66

and we don't need :
Code: 
[COLOR=#ff0000]      ElseIf i = 65 Then
        x = 900
        y = 144

    End If[/COLOR]
 
Last edited:
Upvote 0
Hello

As this is my first time using controls at run-time. i am finding difficult for textboxes references
when pressed on button and calling Sub GetRecord i get Error Method or Data Member not found
Basically how can i reference it(textboxes) to avoid the error.

Code: 
Private Sub cmdButton_Click()
      GetRecord curRow
End Sub

Sub GetRecord(ByVal row As Long)

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet1")


Dim i As Integer
    UserForm1.Tag = xlOff
    Ws.Activate


Dim txtB1 As MSForms.Control
For i = 1 To 65 
   UserForm1.txtB1(i).Value = Worksheets("Sheet1").Cells(3, i).Value
Next i
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
Dim txtbxTopPos As Integer
Dim txtbxGap As Integer

Dim txtB1 As Control
Dim labl As Control

y = 144
x = 10
For i = 1 To 65

    Set txtB1 = Controls.Add("Forms.TextBox.1")
    Set labl = Controls.Add("Forms.Label.1")
    
    If i = 23 Then
        x = 300
        y = 144
        ElseIf i = 46 Then
        x = 600
        y = 144
    End If
    
    With txtB1
        .Name = "txtBox" & i
        .Height = 15.75
        .Width = 116
        .Left = 102 + x
        .Top = y
    End With

    With labl
        .Name = "lbl" & i
        .Height = 15.75
        .Width = 126
        .Left = x
        .Top = y
        .BackStyle = 0
        .Caption = Sheet1.Cells(1, i).Value
    End With
    y = y + 25
Next i
End Sub
 
Last edited:
Upvote 0
NimishK said:
Hello

As this is my first time using controls at run-time. i am finding difficult for textboxes references
when pressed on button and calling Sub GetRecord i get Error Method or Data Member not found
Basically how can i reference it(textboxes) to avoid the error.
Click to expand...

In Private Sub UserForm_Initialize you name the textbox using this line
Code: 
.Name = "txtBox" & i
it means that textbox names are txtBox1, txtBox2, txtBox3... etc
So you can refer it like this:
Code: 
txtBox1.Value = "YES"

If you want to use it in a loop:

Code: 
For i = 1 To 65
   UserForm1.Controls("txtBox" & i).Value = Worksheets("Sheet1").Cells(3, i).Value
Next
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
116
6StringJazzer
6StringJazzer
H
  • Question Question
VBA Project, Copy enabled labels and textboxes from a userform to another userform
Replies
0
Views
376
HosEsf
H
L
  • Question Question
VBA: Dynamical Userform from Module
Replies
0
Views
912
lilcrisis
L
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
H
  • Solved
Reference to Dynamic Label and Textbox in userform
Replies
2
Views
3K
hajiali
H

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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