CODEWITHSUNDEEP
excelvbauserform
TonyMono
TonyMono

Reputation: 1

address textbox in dynamic userform

I have read a lot here so far and I have been helped a lot. Thanks for that! Now there is one thing I can't get further with.

I want to create a userform dynamically in Excel/VBA. This works so far, here an example how I create 5 TextBoxes with Names TextBox1-5. 

                For cTextBox = 1 To 5

                Set edtBox_n = usrFrm.Controls.Add("Forms.textbox.1", "TextBox" & cTextBox)

                    With edtBox_n
                        .Top = nTop
                        .Left = 200
'                        .MultiLine = True
'                        .EnterKeyBehavior = True
                        .Height = 20
                        .Width = 150
                        .Text = .Name
                        '.Name = "Textbox" & cTextBox

                    End With


                    nTop = nTop + 20

                Next cTextBox

But I cannot address the Textboxes with the command e.g. 

Sub CommandButton1_Click()

test = usrFrm.TextBox1.Value

End Sub

Does anyone have an idea? Excel says that TextBox does not exist / the object does not exist. Thanks a lot!

Upvotes: 0

Views: 344

Answers (1)

Tom
Tom

Reputation: 9878

The way I handle this is by creating a class which will handle a lot of the creation of the Control and also the Events. e.g. Class ctrlTextBox

Option Explicit

Public WithEvents edtBox_n As MSForms.TextBox
Private UForm As UserForm

Public Sub Initialize(frm As UserForm, nme As String)
    Set UForm = frm
    Set editbox_n = UForm.Controls.Add(bstrProgID:="Forms.TextBox.1", Name:=nme)
End Sub
Private Sub edtBox_n_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox edtBox_n.value
End Sub

The in my Userform I first declare a public Collection that is available throughout the form. I will store all of my dynamically created controls in here. 

Option Explicit
Private cControls As Collection
Private Sub UserForm_Initialize()
    Dim cTextBox As Long
    Dim edtBox_n As ctrlTextBox
    Dim nTop As Long

    Set cControls = New Collection

    For cTextBox = 1 To 5

        Set edtBox_n = New ctrlTextBox
        edtBox_n.Initialize frm:=Me, nme:="TextBox" & cTextBox

        With edtBox_n.edtBox_n
            .Top = nTop
            .Left = 200
            '                        .MultiLine = True
            '                        .EnterKeyBehavior = True
            .Height = 20
            .Width = 150
            .Text = .Name
        End With

        cControls.Add edtBox_n

        nTop = nTop + 20

    Next cTextBox

End Sub

Upvotes: 2

Related Questions