I am using this macro to collate the options selected in my userform listbox (lstName) and copy them to the next blank row on my spreadsheet (train_db)
However, I want the values in some other items in my userform (Ent2 - Ent5) to be replicated for as many multiselect options are selected in the listbox and copied to train_db also.
i.e. If 3 items are selected from lstName then the spreadsheet should be populated as such...
A B C D E
lstNameoption1 Ent2.Value Ent3.Value Ent4.Value Ent5.Value
lstNameoption2 Ent2.Value Ent3.Value Ent4.Value Ent5.Value
lstNameoption3 Ent2.Value Ent3.Value Ent4.Value Ent5.Value
The current code works fine to generate new records in column A for as many lstName options are selected but I am struggling to populate columns B - E with the values from my other items.
The code in red populates the first new row only. Can I put a simple loop on this to populate all rows equal to the number of listbox options selected, maybe?
![]()
Private Sub cmdAdd_Click() 'declare the variables Dim nextrow As Range 'find the next row to add data to Set nextrow = Sheets("train_db").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 'collate all selected listbox options ReDim ary(0 To 0) With Me.lstName For i = 0 To .ListCount - 1 If .Selected(i) Then ReDim Preserve ary(1 To UBound(ary) + 1) ary(UBound(ary)) = .List(i) End If Next End With 'add values to the database With nextrow .Resize(UBound(ary)).Value = Application.Transpose(ary) .Offset(0, 1) = Ent2.Value .Offset(0, 2) = Ent3.Value .Offset(0, 3) = Ent4.Value .Offset(0, 4) = Ent5.Value End With End Sub
Bookmarks