Dependent Listboxes

[mdiz777]

First off, please accept my apologies if this has already been answered or is a common thing--I've read so many posts here, on other forums, using AI, and stack exchange that have just failed to work. It should also be noted that I have something for this that works using comboboxes, but has been met with criticism due to the extra clicks... I know...

I have a couple of listboxes that are dependent, where the first is populated from a lookup table on a sheet, and a selection causes the second box (using a click method) to populate. The form is saved into a table row on a sheet. This works great.

The issue I have, is editing that row.

I can open the edit form, and the first listbox is always populated, with the correct item selected. The secondary listbox seemingly has no constant behaviour. Sometimes it has the correct dependent items listed, but nothing selected, sometimes it has nothing listed at all, and sometimes it just works as intended. Many thanks in anticipation of any help anyone can offer.

Rules:

• The first listbox items are unlikely to, but may change... that's why they need to be named and not done using listindex or whatever
• The second listbox is the same, unlikely to change but may
• The storage on the sheet is just in plain text due to the above

Code:

Initialising the edit form:

Private Sub UserForm_Initialize()

    ' Load the information
    Call LoadFactor
    
End Sub

To get the dependency list listed in the second listbox:

Private Sub lbFactorList_AfterUpdate()
Dim x As String

x = Me.lbFactorList.Value

    Select Case x
        Case Is = "Accommodation"
            Me.lbFactorType.RowSource = shLookup.ListObjects("tblFacAccommodation")
        Case Is = "Age"
            Me.lbFactorType.RowSource = shLookup.ListObjects("tblFacAge")

    ' there are about 30 of these...

    End Select
End Sub

Attempting to get the secondary list, and the selection (this is the thing that's not working):

Private Sub LoadFactor()

Dim lo As ListObject
Dim findStr As Integer
Dim rw As Range
Dim i As Integer

Set lo = shFactors.ListObjects("tblFactors")

findStr = factorID

    ' Find the person id and details row in the table
    Set rw = lo.DataBodyRange.Find( _
            what:=findStr, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext _
        )

    ' Setup default parameters
    Me.txtRowID.Value = factorID
    Me.txtFactorMasterID.Value = personID 'Global declaration

    ' Display information in the form
    With Me
        With rw
            For i = 1 To lbFactorList.ListCount
                If InStr(rw.Cells(1, lo.ListColumns("Factor").Index).Value, (lbFactorList.List(i - 1))) > 0 Then
                    lbFactorList.Selected(i - 1) = True
                End If
            Next
            For i = 1 To lbFactorType.ListCount
                If InStr(rw.Cells(1, lo.ListColumns("Type").Index).Value, (lbFactorType.List(i - 1))) > 0 Then
                    lbFactorType.Selected(i - 1) = True
                End If
            Next
            For i = 1 To lbFactorStatus.ListCount
                If InStr(rw.Cells(1, lo.ListColumns("Status").Index).Value, (lbFactorStatus.List(i - 1))) > 0 Then
                    lbFactorStatus.Selected(i - 1) = True
                End If
            Next

            txtFactorComment = .Cells(1, lo.ListColumns("Comment").Index).Value

        End With
    End With

End Sub

 

[mdiz777]

I still don't have a solution, just some progress and further information... the secondary listbox is just not showing the selection. Click Save on the form, and the information is saved on the results sheet, it's just not showing as selected on the form. Any help or explanation / information would be greatly appreciated.