Reference to Dynamic Label and Textbox in userform

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
626
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Private Sub userform_initialize()
Dim i As Long
number = Worksheets("RESULTS").Range("SS1").Value
Dim LbL1, LbL2, LbL3 As Object
Dim txtB1 As Control
Dim CmB1 As CommandButton
UserForm9.Height = 30 * number + 90
UserForm9.Width = 450
UserForm9.ScrollBars = fmScrollBarsNone

If number > 10 Then
    UserForm9.ScrollBars = fmScrollBarsVertical
    UserForm9.Height = 370
    UserForm9.ScrollHeight = 30 * number + 90
End If

For i = 1 To number
        Set LbL1 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL1
                .Caption = Sheets("RESULTS").Cells(i, "ST")
                .Name = "Enum" & i
                .Height = 10
                .Left = 30
                .Width = 50
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set LbL2 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL2
                .Caption = Sheets("RESULTS").Cells(i, "SU")
                .Name = "Ename" & i
                .Height = 10
                .Left = 95
                .Width = 120
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set LbL3 = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            With LbL3
                .Caption = Format(Sheets("RESULTS").Cells(i, "SV"), "(###) ###-####")
                .Name = "Phone" & i
                .Height = 10
                .Left = 230
                .Width = 60
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
        Set txtB1 = Controls.Add("Forms.TextBox.1")
            With txtB1
                .Name = "txtBox" & i
                .Height = 20
                .Width = 25
                .Left = 310
                .Top = 20 * i * 1.5
            End With
Next i
 Set CmdBtn = UserForm9.Controls.Add("Forms.CommandButton.1")
    With CmdBtn
        .Caption = "ENTER"
        .Name = "CmdBtn"
        .Left = 290
        .Top = 30 * number + 30
        .Width = 70
    End With
  Controls("txtbox" & 1).SetFocus
End Sub

With the above code Im able to get what I need as whats in the attached picture.

I am need to refer to the Labels and the textboxes of the dynamic userform above.

in the picture the labels are LBL1 and the textboxes to the right are the txtB1

below is what I have but does not seem to work:

VBA Code:
Private Sub CmdBtn_Click()
Dim r, i As Long
Dim LbL1, LbL2, LbL3 As Object
Dim txtB1 As Control
With Sheets("DATA").Activate
    For i = 1 To number
        r = Range("E1").Value
        .Cells(r, 5) = LbL1.Enum1.Text
        .Cells(r, 6) = Controls("txtbox" & 1).Value
   Next i
End With
MsgBox "Done", vbOKOnly
End Sub

any help is greatly appreciated
 

Attachments

  • Capture.JPG
    Capture.JPG
    11.8 KB · Views: 47

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Firstly, Just draw to your attention your variable declarations

Code:
Dim r, i As Long

Dim LbL1, LbL2, LbL3 As Object

When you declare multiple variables in a single line Dim statement you must explicitly declare each variables data type otherwise, only the last variable in the line is of the specified data type – all others before it will be variants.

Code:
Dim r As Long, i As Long

Dim LbL1 As Object, LbL2 As Object, LbL3 As Object



To get the button added on the fly to work you will need to create an object using withevents then you can assign it to the object with events.

I have made a vain attempt to update your code (and included the WithEvents for the button) but it is just that, you will need to test & adjust as required to meet project need or maybe another here can offer an improved solution for you.



Code:
Public WithEvents cmdBtn     As MSForms.CommandButton
Dim Number                   As Long
Dim wsData                   As Worksheet, wsRESULTS  As Worksheet

Private Sub UserForm_Initialize()
    Dim labelCounter    As Long, i As Long, j As Long
    Dim LbL             As Object
    Dim txtB1           As Control
    Dim cmb1            As CommandButton
    
    Set wsRESULTS = ThisWorkbook.Worksheets("RESULTS")
    
    Set wsData = ThisWorkbook.Worksheets("DATA")
    
    Number = Val(wsRESULTS.Range("A1").Value)
    If Number = 0 Then Number = 1
    
    Me.Height = 30 * Number + 90
    Me.Width = 450
    Me.ScrollBars = fmScrollBarsNone
    
    If Number > 10 Then
        Me.ScrollBars = fmScrollBarsVertical
        Me.Height = 370
        Me.ScrollHeight = 30 * Number + 90
    End If
    
    For i = 1 To Number
        For j = 1 To 3
            labelCounter = labelCounter + 1
            Set LbL = Controls.Add("Forms.Label.1", "Test" & labelCounter, True)
            
            With LbL
                .Caption = wsRESULTS.Cells(i, Choose(j, "ST", "SU", "SV")).Text
                .Height = 10
                .Left = Choose(j, 30, 95, 230)
                .Width = Choose(j, 50, 120, 60)
                .Top = 30 * i + 6
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignRight
            End With
            
            Set LbL = Nothing
        Next j
        
        Set txtB1 = Controls.Add("Forms.TextBox.1")
        With txtB1
            .Name = "txtBox" & i
            .Height = 20
            .Width = 25
            .Left = 310
            .Top = 20 * i * 1.5
        End With
        Set txtB1 = Nothing
    Next i
    
    Set cmdBtn = Me.Controls.Add("Forms.CommandButton.1")
    With cmdBtn
        .Caption = "ENTER"
        .Name = "CmdBtn"
        .Left = 290
        .Top = 30 * Number + 30
        .Width = 70
    End With
    Controls("txtbox" & 1).SetFocus
End Sub

Private Sub CmdBtn_Click()
    Dim r As Long, i As Long
    
    With wsData
        r = .Range("E1").Value
        For i = 1 To Number
            .Cells(r, 5) = Me.Controls("Test" & i).Caption
            .Cells(r, 6) = Me.Controls("txtbox" & i).Value
            r = r + 1
        Next i
    End With
    
    MsgBox "Done", vbOKOnly, "All Done"
End Sub

Hope Helpful

Dave
 
Upvote 0
Solution
thank you dmt32 for that information and help. I will give that a try on Friday when I’m back on my computer. I will let you know the outcome.


Thanks
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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