Set Control = Object.Add(ProgID [, Name [, Visible]])

Private Sub UserForm_Initialize()
  Dim objTextBox As MSForms.TextBox

  Set objTextBox = Me.Controls.Add( _
        "Forms.TextBox.1", "txtDemo1", True)
  With objTextBox
    .Left = 6
    .Top = 6
    .Width = 72
    .Height = 18
  End With
  Set objTextBox = Nothing
End Sub

MsgBox Me.Controls("txtDemo1").Text

MsgBox Me!txtDemo_1.Text

MsgBox Me.txtDemo_1.Text
MsgBox txtDemo_1.Text

Option Explicit

Private m_objTextBox As MSForms.TextBox

Private Sub UserForm_Initialize()
  Set m_objTextBox = Me.Controls.Add("Forms.TextBox.1", , True)
  With m_objTextBox
    .Left = 6
    .Top = 6
    .Width = 72
    .Height = 18
  End With
End Sub

MsgBox m_objTextBox.Text

Object.Remove(collectionindex)

Me.Controls.Remove "txtDemo_1"

Me.Controls.Remove 0

Me.Controls.Remove m_objTextBox.Name

Private WithEvents mevt_TextBox As MSForms.TextBox

  - AfterUpdate
  - BeforeUpdate
  - Enter
  - Exit

Option Explicit

Private WithEvents mevt_TextBox As MSForms.TextBox

Private Sub UserForm_Initialize()
  Set mevt_TextBox = Me.Controls.Add("Forms.TextBox.1", , True)
  mevt_TextBox.Move 6, 6, 72, 18
End Sub

Private Sub mevt_TextBox_Change()
  MsgBox "In der TextBox '" & mevt_TextBox.Name & _
        "' wurde eine Eingabe gemacht!" & vbCr & _
        "Text: " & mevt_TextBox.Text
End Sub

Option Explicit

Private WithEvents mevt_TextBox1 As MSForms.TextBox
Private WithEvents mevt_TextBox2 As MSForms.TextBox

Private Sub UserForm_Initialize()
  Set mevt_TextBox1 = Me.Controls.Add("Forms.TextBox.1", , True)
  mevt_TextBox1.Move 6, 6, 72

  Set mevt_TextBox2 = Me.Controls.Add("Forms.TextBox.1", , True)
  mevt_TextBox2.Move mevt_TextBox1.Left, mevt_TextBox1.Top + _
      mevt_TextBox1.Height + 3, mevt_TextBox1.Width
End Sub

Private Sub mevt_TextBox1_Change()
  TextBox_Change_Event mevt_TextBox1
End Sub

Private Sub mevt_TextBox2_Change()
  TextBox_Change_Event mevt_TextBox2
End Sub

Private Sub TextBox_Change_Event(ByRef objTB As MSForms.TextBox)
  MsgBox "In der TextBox '" & objTB.Name & _
        "' wurde eine Eingabe gemacht!" & vbCr & _
        "Text: " & objTB.Text
End Sub

Private WithEvents mevt_TextBox As MSForms.TextBox

Private Sub mevt_TextBox_Change()

End Sub

Option Explicit

Private WithEvents mevt_TextBox As MSForms.TextBox
Private m_objForm As Object

Private Sub Class_Initialize()
  '
End Sub

Private Sub Class_Terminate()
  Set mevt_TextBox = Nothing
  Set m_objForm = Nothing
End Sub

Private Sub mevt_TextBox_Change()
  If Not m_objForm Is Nothing Then
    Call m_objForm.TextBox_Change_Event(mevt_TextBox)
  End If
End Sub

Public Property Set Form(ByRef objForm As Object)
  Set m_objForm = objForm
End Property

Public Property Set TextBox(ByRef objTextBox As MSForms.TextBox)
  Set mevt_TextBox = objTextBox
End Property

Option Explicit

Private mcol_TextBoxes  As Collection
Private m_objForm       As Object

Private Sub Class_Initialize()
  Set mcol_TextBoxes = New Collection
End Sub

Private Sub Class_Terminate()
  Set mcol_TextBoxes = Nothing
  Set m_objForm = Nothing
End Sub

Public Property Set Form(ByRef objForm As Object)
  Set m_objForm = objForm
End Property

Public Function Add(ByRef objTextBox As MSForms.TextBox) _
      As CTextBox

  Dim objCTextBox As CTextBox

  On Error GoTo err_Add
  Set objCTextBox = New CTextBox
  With objCTextBox
    Set .Form = m_objForm
    Set .TextBox = objTextBox
  End With

  mcol_TextBoxes.Add objCTextBox, objTextBox.Name
  Set Add = objCTextBox

  Set objCTextBox = Nothing

exit_Func:
  On Error GoTo 0
  Exit Function

err_Add:
  MsgBox "Fehler: " & Err.Number & vbCrLf & _
          Err.Description, vbOKOnly + vbCritical

  Resume exit_Func
End Function

Public Property Get Item(ByVal Index As Variant) As CTextBox
  Set Item = mcol_TextBoxes.Item(Index)
End Property

Public Property Get Count() As Long
  Count = mcol_TextBoxes.Count
End Property

Public Sub Remove(ByVal Index As Variant)
  mcol_TextBoxes.Remove Index
End Sub

Option Explicit

Private Const mc_sngBorder  As Single = 6
Private Const mc_sngGapV    As Single = 3

Private Const mc_sngTxtLeft As Single = 6
Private Const mc_sngTxtWdth As Single = 132
Private Const mc_sngTxtHght As Single = 18

Private mcol_TextBoxes As col_CTextBox

Private Sub UserForm_Initialize()
  Dim objTextBox  As MSForms.TextBox

  Dim sngPosTop   As Single
  Dim i           As Integer

  Set mcol_TextBoxes = New col_CTextBox
  Set mcol_TextBoxes.Form = Me

  sngPosTop = mc_sngBorder
  For i = 1 To 3
    Set objTextBox = Me.Controls.Add("Forms.TextBox.1", , True)
    With objTextBox
      .Move mc_sngTxtLeft, sngPosTop, mc_sngTxtWdth, mc_sngTxtHght
      sngPosTop = sngPosTop + .Height + mc_sngGapV
    End With

    mcol_TextBoxes.Add objTextBox
    Set objTextBox = Nothing
  Next

  Me.Height = (Me.Height - Me.InsideHeight - 3) + sngPosTop + _
        mc_sngBorder
  Me.Width = (Me.Width - Me.InsideWidth) + mc_sngTxtWdth + _
        (2 * mc_sngTxtLeft)
End Sub

Private Sub UserForm_Terminate()
  Set mcol_TextBoxes = Nothing
End Sub

Public Sub TextBox_Change_Event(ByRef objTextBox As _
      MSForms.TextBox)
  With objTextBox
    If Len(Trim$(.Text)) > 0 Then
      If IsNumeric(.Text) Then
        .ForeColor = vbBlue
      Else
        .ForeColor = vbRed
      End If
    End If
  End With
End Sub

In Demo 1 werden die Labels und TextBoxen untereinander zur UserForm hinzugefügt und anschließend die Höhe der UserForm an die Anzahl/Höhe der TextBoxen plus Abstand angepasst.

Werden beispielsweise je 20 Labels und TextBoxen untereinander zur UserForm hinzugefügt, könnte es jedoch sein, dass je nach Bildschirmgröße und Auflösung ein Teil der UserForm für den Anwender nicht mehr sichtbar/erreichbar ist. Zu empfehlen ist diese Vorgehensweise also nicht.

Abhilfe könnte hier die vertikale ScrollBar der UserForm schaffen, wie in Demo 2 gezeigt.

Allerdings sollte man bedenken, dass andere ggf. vorhandene Steuerelemente, hier beispielsweise der obere Frame mit der TextBox und dem Button 'Demo', auch mitgescrollt werden. Das heißt also, damit der Frame inkl. Steuerelemente in den sichtbaren Bereich verschoben wird, müsste der Anwender ggf. die UserForm scrollen. Die "Ideallösung" ist das also auch nicht.

Sinnvoller wäre es m. E., statt ggf. die vertikale ScrollBar der UserForm einzublenden, für den dynamischen Bereich ein Frame hinzuzufügen. In Demo 3 werden in der Entwicklungsumgebung zur Entwurfszeit drei Frames zur UserForm hinzugefügt.

Der obere Frame bleibt zur Laufzeit immer an der zugewiesenen Position und in der Größe gleich.

Der mittlere, dynamische Frame wird ggf. in der Höhe angepasst, je nachdem wie viele Labels und TextBoxen hinzugefügt werden. Es wurde hier jedoch eine maximale Höhe für den Frame festgelegt. Werden mehr als vier Steuerelementpaare hinzugefügt, wird die vertikale ScrollBar des Frames angezeigt und die TextBoxen in der Breite entsprechend angepasst. Es sind also nie mehr als vier Steuerelementpaare sichtbar. Werden keine Steuerelemente hinzugefügt, wird der Frame ausgeblendet.

Der untere Frame wird ggf. nach oben oder unten verschoben, je nach Höhe des mittleren, dynamischen Frames, bleibt jedoch in der Größe immer gleich. Beim Initialisieren der UserForm wird die Darstellung des Frame insoweit geändert, so dass der Rahmen nicht sichtbar ist.

Option Explicit

Private Const mc_intMaxCtls As Integer = 20

Private Const mc_sngFrameMaxHght As Single = 96

Private Const mc_sngBorder  As Single = 6
Private Const mc_sngGapV    As Single = 3

Private Const mc_sngLblLeft As Single = 6
Private Const mc_sngLblWdth As Single = 30

Private Const mc_sngTxtLeft As Single = 42
Private Const mc_sngTxtWdth As Single = 124
Private Const mc_sngTxtHght As Single = 18

Private ma_Labels()         As MSForms.Label
Private ma_TextBoxes()      As MSForms.TextBox

Private m_intCurNumCtls     As Integer

Private Sub AddControls(ByVal intNumCtls As Integer)
  Dim sngPosTop  As Single
  Dim i          As Integer

  Select Case intNumCtls
    Case Is < 0

    Case Is = m_intCurNumCtls

    Case Is > mc_intMaxCtls
      MsgBox "Maximal " & CStr(mc_intMaxCtls) & _
             " Steuerelemente möglich!"

    Case Is < m_intCurNumCtls
      For i = m_intCurNumCtls To intNumCtls + 1 Step -1
        Me.Controls.Remove ma_Labels(i).Name
        Me.Controls.Remove ma_TextBoxes(i).Name
      Next

      ReDim Preserve ma_Labels(0 To intNumCtls)
      ReDim Preserve ma_TextBoxes(0 To intNumCtls)

      m_intCurNumCtls = intNumCtls
      ResizeControls

    Case Is > m_intCurNumCtls
      If m_intCurNumCtls > 0 Then
        With ma_TextBoxes(m_intCurNumCtls)
          sngPosTop = .Top + .Height + mc_sngGapV
        End With
      Else
        sngPosTop = mc_sngBorder
      End If

      ReDim Preserve ma_Labels(0 To intNumCtls)
      ReDim Preserve ma_TextBoxes(0 To intNumCtls)

      For i = m_intCurNumCtls + 1 To intNumCtls
        Set ma_Labels(i) = Me.fraDyn.Controls.Add( _
              "Forms.Label.1", , True)
        With ma_Labels(i)
          .Move mc_sngLblLeft, sngPosTop + 3, mc_sngLblWdth
          .Caption = "Pos. " & CStr(i)
        End With

        Set ma_TextBoxes(i) = Me.fraDyn.Controls.Add( _
              "Forms.TextBox.1", , True)
        With ma_TextBoxes(i)
          .Move mc_sngTxtLeft, sngPosTop, , mc_sngTxtHght
          .TabStop = False
          sngPosTop = sngPosTop + .Height + mc_sngGapV
        End With
      Next

      m_intCurNumCtls = intNumCtls
      ResizeControls

    Case Else
  End Select
End Sub

Private Sub ResizeControls()
  Dim sngHght     As Single
  Dim sngTxtWidth As Single
  Dim i           As Integer

  Dim objCtl      As MSForms.Control

  If m_intCurNumCtls = 0 Then
    Me.fraDyn.Visible = False
    Set objCtl = Me.fraMain

  Else
    Me.fraDyn.Visible = True
    Set objCtl = Me.fraDyn

    With ma_TextBoxes(m_intCurNumCtls)
      sngHght = .Top + .Height + mc_sngBorder
    End With

    If sngHght <= mc_sngFrameMaxHght + mc_sngBorder Then
      With Me.fraDyn
        .ScrollTop = 0
        .ScrollBars = fmScrollBarsNone
        .Height = (.Height - .InsideHeight) + sngHght
      End With
      sngTxtWidth = mc_sngTxtWdth

    Else
      With Me.fraDyn
        .Height = mc_sngFrameMaxHght - mc_sngGapV
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = sngHght

        sngTxtWidth = mc_sngTxtWdth - (.Width - .InsideWidth - 3)
      End With
    End If

    For i = 1 To m_intCurNumCtls
      ma_TextBoxes(i).Width = sngTxtWidth
    Next
  End If

  Me.fraButtons.Top = objCtl.Top + objCtl.Height + mc_sngBorder
  Set objCtl = Nothing

  Me.Height = (Me.Height - Me.InsideHeight - 3) + _
        Me.fraButtons.Top + Me.fraButtons.Height + mc_sngBorder
End Sub