Dynamicly add new or remove a line of comboboxes in Userform also a refernce code to handle those comboboxes input

Question

I have this userform

1. The form contains three comboboxes along with "add new" command button

2. Once user click add new button it will add another row of comboboes with a clear button.

3. The clear button will remove that particular row of comboboxes only. add button will add a new line below this line

4. each row is an element i.e A,B,C... The insert button will write conditions e.g. A OR B OR C inserted text gathered from comboboxes would be {#(ProductA === "P25" || ProductB === "P26" || ProductC === "P27")}

I am trying to have a button that can dynamically add new line of comboboxes with a clear button. also clear button that will only remove that line of comboboxes. I can user the code as discussed here. But don't know how I can handle the position and clear button.

Answer 1

An interesting mini-project, so here is my "solution" (obviously still far from being fully-functional but has the basic add/remove row capability.

The userform I tested with had only a single command button btnAddRow (for adding new rows).

Class DataRow:

Option Explicit

Private WithEvents Combo1 As MSForms.ComboBox
Private WithEvents Combo2 As MSForms.ComboBox
Private WithEvents ClearButton As MSForms.CommandButton

Dim controlContainer As Object
Public id As Long

Private Sub ClearButton_Click()
    'calls a method on the parent form to remove the row
    CallByName controlContainer, "RemoveRow", VbMethod, id
End Sub


'create and set up a few properties the controls for a row
Public Sub Create(container As Object, rowId As Long)
    
    Set Combo1 = container.Controls.Add("Forms.ComboBox.1", "C1_" & rowId)
    Combo1.Left = 20
    Combo1.Width = 60
    Combo1.List = Array("Item1", "Item2")
    
    Set Combo2 = container.Controls.Add("Forms.ComboBox.1", "C2_" & rowId)
    Combo2.Left = 90
    Combo2.Width = 60
    Combo2.List = Array("Red", "Blue")
    
    Set ClearButton = container.Controls.Add("Forms.commandbutton.1", "BC_" & rowId)
    ClearButton.Left = 160
    ClearButton.Width = 50
    ClearButton.Height = 20
    ClearButton.Caption = "Clear"
    
    Set controlContainer = container 'keep a reference to the place where the controls are created
    id = rowId                       'identifies this row
End Sub

'set the vertical position on the form for the row
Public Sub Position(rowIndex)
    Dim pTop
    pTop = 15 + (25 * (rowIndex - 1))
    Combo1.Top = pTop
    Combo2.Top = pTop
    ClearButton.Top = pTop
End Sub

'remove the row - called from the parent form
Public Sub Remove()
    With controlContainer.Controls
        .Remove Combo1.Name
        .Remove Combo2.Name
        .Remove ClearButton.Name
    End With
End Sub

'move the "add row" button to this row
Public Sub TakeAdd(btn As MSForms.CommandButton)
    btn.Left = ClearButton.Left + ClearButton.Width + 10
    btn.Top = ClearButton.Top
End Sub

'is this row removable? (last remaining row can't be removed)
Public Sub CanRemove(bRemovable As Boolean)
    ClearButton.Enabled = bRemovable
End Sub

The code for the userform:

Option Explicit

Dim id As Long              'sequence number for assigning unique id to each row
Dim col As New Collection   'holds instances of the class `DataRow`

Private Sub btnAddRow_Click()
    AddRow 'add a new row
End Sub

Private Sub UserForm_Activate()
    AddRow 'add starting row
End Sub

'add a row
Sub AddRow()
    Dim obj As New DataRow
    id = id + 1
    obj.Create Me, id          'creates the controls
    col.Add obj                'add to the collection
    obj.Position col.Count     'sets row index on form
    obj.TakeAdd Me.btnAddRow   'position the "add row" button
    checkRemove                'check if remove buttons are enabled
End Sub

'remove the specified row
Sub RemoveRow(rowId As Long)
    Dim i As Long
    i = 1
    Do While i <= col.Count
        If col(i).id = rowId Then   'is this the row to remove?
            col(i).Remove           'removes the row from the form
            col.Remove (i)          'removes the class instance from the collection
        Else
            col(i).Position i       '(re)position this row
            i = i + 1
        End If
    Loop
    col(col.Count).TakeAdd Me.btnAddRow '(re)position the "add row" button
    checkRemove        'check if remove buttons are enabled
End Sub

'see if the "remove" buttons should be enabled
Sub checkRemove()
    Dim obj
    For Each obj In col
        obj.CanRemove (col.Count > 1) 'enabled if >1 row on form
    Next obj
End Sub