Unique Entries in Userform Dependent Listboxes


Unique Entries in Userform Dependent Listboxes

The relationship listbox template has been used exactly zero times. One can build their classes from start along with names which actually reflect the objects of the business they are aimed at representing. The userform can be re-used. In addition, you do not have to change the control names to Class/Student from Parent/Child.

Consider a scenario where-in, you have certain students as well as classes. Several students can be present in a class and several classes can be taken up by a single student

The userform is responsible for listing the students as soon as the class is selected. The userform will list all of the students from the classes which have been selected, in case more than a single class is selected. However, each student can be only listed once. 

You can see that the students Andrew as well as Payton are listed only once.

Certain changes have to be made in the code. In addition of tracking ActiveParent, you can also track ActiveClasses as the top listbox is currently multiselected. When there is a change in the Parent listbox, you have to observe the classes which are selected.

Private Sub lbxParents_Change()

    Dim clsClass As CClass
    Dim i As Long

    If Me.lbxParents.ListIndex <> -1 Then
        Set Me.ActiveClasses = New CClasses
        For i = 0 To Me.lbxParents.ListCount - 1
            If Me.lbxParents.Selected(i) Then
                Me.ActiveClasses.Add Me.Classes.ClassByClassName(Me.lbxParents.List(i))
            End If
        Next i
    Else
        Set Me.ActiveClasses = Nothing
    End If

    FillChildren

End Sub

Private Sub FillChildren()

    Me.lbxChildren.Clear

    If Not Me.ActiveClasses Is Nothing Then
        If Me.ActiveClasses.StudentCount > 0 Then
            Me.lbxChildren.List = Me.ActiveClasses.StudentList
            Me.lbxChildren.ListIndex = 0
        End If
    End If

End Sub

A dictionary object can be used to get a student file which is unique. One of the best aspects about dictionaries is with respect to returning a zero-based array from the properties of Items as well as keys.

Public Property Get StudentList() As Variant

    Dim clsClass As CClass
    Dim clsStudent As CStudent
    Dim dcReturn As Scripting.Dictionary

    Set dcReturn = New Scripting.Dictionary
    
    For Each clsClass In Me
        For Each clsStudent In clsClass.Students
            If Not dcReturn.Exists(clsStudent.StudentName) Then
                dcReturn.Add clsStudent.StudentName, clsStudent.StudentName
            End If
        Next clsStudent
    Next clsClass
    
    StudentList = dcReturn.Keys
    
End Property