Programmatically add and delete Combobox, Textbox and Checkbox to Userform - VBA

Question

I am creating a userform which has 1 combobox (1), 2 textboxes (2 and 3), and 11 checkboxes (A-K) as in picture. The first instance of all controls will be present, but when a user clicks on Add Class, a new set of all controls has to be created and when Remove Class should delete a particular row.

I have managed to get the requirement for one instance, but I could not figure out how do I do it for N times, with the size of the userform to be expanding also.

Private Sub cmdAddClass()

 Dim cCheckBox As Control, r As Long, r1 As Range

 Set r1 = Sheets("Sheet1").Range("A2:A12") 

 Set cComboBox = Me.Controls.Add("Forms.ComboBox.1")
 With cComboBox
  .Height=25.5
  .Width=102
  .Top=50
  .Left =6
 End With

 Set cTextBox = Me.Controls.Add("Forms.TextBox.1")
 With cTextBox
  .Height=25.5
  .Width=54
  .Top=50
  .Left =114
 End With

 For r = 1 To WorksheetFunction.CountA(r1)
  If r1(r) <> vbNullString Then 
   Set cCheckBox = Me.Controls.Add("Forms.CheckBox.1", "Checkbox" & r, True)
    With cCheckBox
     .Width = 21
     .Height = 11
     .Top = 50
     If r = 1 Then
       .Left = 270
     ElseIf r = 2 Then
       .Left = 348
     ElseIf r = 3 Then
       .Left = 420
     ElseIf r = 4 Then
       .Left = 492
     ElseIf r = 5 Then
       .Left = 564
     ElseIf r = 6 Then
       .Left = 636
     ElseIf r = 7 Then
       .Left = 701.95
     ElseIf r = 8 Then
       .Left = 780
     ElseIf r = 9 Then
       .Left = 876
     ElseIf r = 10 Then
       .Left = 966
     Else
       .Left = 1050
     End If
    End With
  End If
 Next r
End Sub

Answer 1

Here is an example you can adapt.

Private Sub btnAddClass_Click()
    Dim ctrl As Control, newCtrl As Control, offsetTop As Integer

    offsetTop = 30

    For Each ctrl In Me.Controls

        If TypeName(ctrl) <> "CommandButton" Then
            If ctrl.Top = btnAddClass.Top - offsetTop Then
                If TypeName(ctrl) = "ComboBox" Then
                    Set newCtrl = Me.Controls.Add("Forms.ComboBox.1")
                ElseIf TypeName(ctrl) = "TextBox" Then
                    Set newCtrl = Me.Controls.Add("Forms.TextBox.1")
                ElseIf TypeName(ctrl) = "CheckBox" Then
                    Set newCtrl = Me.Controls.Add("Forms.Checkbox.1")
                End If

                With newCtrl
                    .Height = ctrl.Height
                    .Width = ctrl.Width
                    .Top = ctrl.Top + offsetTop
                    .Left = ctrl.Left
                End With

            End If
        End If
    Next ctrl

    btnAddClass.Top = btnAddClass.Top + offsetTop
    btnRemoveClass.Top = btnRemoveClass.Top + offsetTop
    Me.Height = Me.Height + offsetTop
End Sub

Private Sub btnRemoveClass_Click()
    Dim ctrl As Control, offsetTop As Integer

    offsetTop = 30

    For Each ctrl In Me.Controls

        If TypeName(ctrl) <> "CommandButton" Then
            If ctrl.Top = btnAddClass.Top - offsetTop Then
                Me.Controls.Remove (ctrl.Name)
            End If
        End If

    Next ctrl

    btnAddClass.Top = btnAddClass.Top - offsetTop
    btnRemoveClass.Top = btnRemoveClass.Top - offsetTop
End Sub

Notes:

To get this to work I need to explain the set-up:

1. Your initial row of controls have a Top property set to 12
2. There are two buttons named btnAddClass and btnRemoveClass with Top set to 42

The offset in height between the controls and the buttons is 30.

To add new controls you simply loop over each control, create a new one, and set its Top to be the existing control Top value + the offset (i.e. 30). At the same time you shift the buttons down by 30 and increase the userform height by 30.

To remove controls you get the last row by checking the Top property relative to the btnAddClass. You then delete those controls and shift the buttons up by 30 and decrease the userform height by 30.