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

Recommended Answers

All 2 Replies

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.

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.
Click Here
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.

commented: That link is dead. What is it? +16
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.