Combine and copy data from two listboxes to a third

Question

Robert_147 16 Newbie Poster

4 Years Ago

Hello,

I am fairly new to VBA development trying to upgrade my employee training database in VBA from a single listbox with single line select/add/edit capability to one that pulls employee data from listbox1, searches training available by job title in listbox2, and combines the data into listbox3 also adding the combined data to an Excel database of completed training. See Pic Below

Ideally, I would like to be able to select multiple people in listbox1 and combine to a single training event in listbox2, and likewise, be able to select multiple training activities in listbox2 that can be combined with a single student in listbox1.

Finally, I need listbox3 to be able to search the completed training records allowing a user to doubleclick the record, edit, and save.

I have created the original tracker database and searchable listbox and the advanced filters to pull data into listboxes 1 & 2, but I am lost on how to select the data in the two listboxes to combine in the third.

Any help is appreciated

database-design microsoft:access visual-basic:vba

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with vnd.openxmlformats-officedocument.wordprocessingml.document files.

Tracker_Pic.docx (399.07 KB)

Edited 3 Years Ago by Dani because: Formatting fixed

2 Contributors
2 Replies
114 Views
6 Days Discussion Span
Latest Post 4 Years Ago Latest Post by Robert_147

Dani AI

Generated 10 Months Ago

— the simplest, reliable pattern is: collect the selected employee IDs from your multi‑select listbox, get the single selected training ID from the class listbox, build a 2‑dimensional Variant array (one row per employee) that contains the fields you want to show/save, load that array into the consolidated listbox in one operation, then write the same array to the completed‑training sheet in a single Range.Value assignment. This avoids slow row‑by‑row writes and the common AddItem/subitem pitfalls. was right that the original post lacked runnable code; the snippets shown later have a few structural issues (misplaced Next, Row.Count vs Rows.Count, using AddItem when you need multi‑column fills, and failing to maintain a row/key mapping for edits).

A straightforward workflow (pseudocode):

' gather selected employees (IDs and names) into empList
' read selected training into trnData
' create arr(rows = empList.Count, cols = numberOfFields)
' for each employee: fill arr(row, ...) = employee fields + training fields + stagingRowKey
' set lstConsolidate.ColumnCount and assign lstConsolidate.List = arr
' write arr to destination sheet with one Range(...).Resize(...).Value = arr

For edit/double‑click: include a hidden column in the consolidated list (stagingRowKey or a composite key) so the DblClick handler can find the exact sheet row to update, push the change back to the sheet, then update the consolidated listbox row (either by rebuilding the array or by writing the single changed row into the listbox List property).

Troubleshooting tips: always use zero‑based listbox indexes (0 To .ListCount - 1), clear listboxes with .Clear, set .ColumnCount before assigning a multi‑column array, and use Debug.Print to inspect collected IDs. Microsoft docs on the List property and AddItem behavior are useful references: and .

All 2 Replies

rproffitt 3,266 https://5calls.org

4 Years Ago

I don't see any code here. Maybe you are asking for code but it's unclear here as the tags don't mention what language you are using as well as you didn't supply the code you've written along with what stopped you.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Robert_147 16 Newbie Poster · Answer 1 · 2022-04-28T20:42:17+00:00

I am trying to improve my training database but I do not have the knowledge to build the code. I have a command button that runs a advanced search query for both the "Search Employee" and "Search Class/Training" listboxes. These allows me to search any items in the table of employees by header, and search my class listing (700 line items) based on job title, training module, ect.

Problem:
I have 4 listboxes

List box1 (lstActiveEmp): displays employee data, through an advanced filter, from sheet7. This is a multi select listbox allowing a user to select many employees.

Listbox2 (lstTrngSel): displays available classes, through an advanced filter on sheet20, which are assigned to the selected employee(s)in listbox1. This is a single select listbox allowing the many employees to be mass assigned to a single training event. Today this has to be done one employee at a time.

Listbox3 (lstConsolidate): is where I am trying to consolidate listbox 1 & 2 above. I have a commandbutton to add the consolidated training in listbox3 to sheet16 which is a loading page to display in the listbox before adding to the a master list of completed training sheet2.

Listbox4: is used to search/display/edit completed historic training records located on sheet7 through an advanced filter. This all works fine. Combining all the people to the training in listboxes1&2 to listbox3, then saving it to sheet2 for record is the goal.

Is there a way to select multiple people from listbox1 in a userform, select a single class from listbox2 in the same userform, combine them into listbox3 to review before adding to the master database of completed training? I have tried to splice so many different codes together with no luck.

Current code I have and am trying to work through:

Private Sub cmdCombineTrng_Click()
Dim Staff_DataSH As Worksheet
Dim Training_ListSH As Worksheet
Dim ClassConsolidationSH As Worksheet
Dim i As Long, j As Variant
    Set Staff_DataSH = Sheet7
    Set Training_ListSH = Sheet20
    Set ClassConsolidationSH = Sheet16
lastrow = Sheet7.Cells(Row.Count, 148).End(xlUp).Row ' advfilter resilts Header Row 8, Columns FR:FV
Clear 'all listboxes
  For selItm = LBound(Me.lstActiveEmp.List) To UBound(Me.lstActiveEmp.List) ' For sheet7 employee list
        Next selItm
    If Me.lstActiveEmp.Selected(selItm) = True Then
        curVal = Me.lstActiveEmp.List(selItm, 0)
        For x = 2 To lastrow
            If Sheet7.Cells(x, "FR") = curVal Then
                Me.lstconsolidate.AddItem Sheet16.Cells(x, "C")
            End If
        Next x
    End If
    For curVal = LBound(Me.lstTrngSel.List) To UBound(Me.lstTrngSel.List) ' For sheet 20 training
        Next curVal
    If Me.lstActiveEmp.Selected(selItm) = True Then
        curVal = Me.lstTrngSel.List(selItm, 0)
        For x = 2 To lastrow
            If Sheet7.Cells(x, "FR") = curVal Then
                Me.lstconsolidate.AddItem Sheet16.Cells(x, "C")
            End If
        Next x
    End If
     curVal = Me.lstTrngSel.Value
    For x = 2 To lastrow
        If Sheet20.Cells(x, "P") = curVal Then
            Me.lstconsolidate.AddItem Sheet16.Cells(x, "I")
        End If
    Next x
End Sub

I have also tried:

With Me
For i = 0 To .lstActiveEmp.ListCount - 1
    If .lstActiveEmp.Selected(i) Then
        Range("FR" & Rows.Count).End(xlUp)(9).Value = .lstActiveEmp.List(i)
        j = j + 1
    End If
Next i
For i = 0 To .lstTrngSel.ListCount - 1
    If .lstTrngSel.Selected(i) Then
        Range("P" & Rows.Count).End(xlUp)(9).Resize(j).Value = .lstTrngSel(i)
    End If
Next i
End With

Any help is appreciated.

Combine and copy data from two listboxes to a third

Dani AI

Recommended Answers Collapse Answers

All 2 Replies

Recommended Answers