[SOLVED] Getting a series of Excel Listbox Controls to Work Together Seamlessly

Getting a series of Excel Listbox Controls to Work Together Seamlessly

I'm developing an application in Excel that allows the user to set up their PLC IO configuration by choosing from a large list of different PLC modules. Modules are chosen by selecting, in order, Manufacturer, Device Family, Device Type, and Module Name. Each attribute selections corresponds to a different ListBox control. The ListFillRange of each ListBox is controlled by the previous ListBox. For example, when I click the first ListBox to choose my Manufacturer, my VBA code clears the previous Device Family ListFillRange, and repopulates it with all Device Families from the newly selected Manufacturer. Also, each ListBox uses a specific range as it's ListFillRange. It might be the case that I can populate each ListBox selection list more directly using VBA but I haven't been able to figure out how to do this. Each ListBox uses the ListBox_Click() event to run its code. The issues I'm having are the following.

It seems that when one ListBox clears the ListFillRange of the next ListBox, it triggers the code of the next ListBox to run and this often causes a "Run-time error '1004': Clear method of Range class failed."
When the code doesn't error out it often resizes the next ListBox to the point that it can't be read.

Below is an example of the code run when the Device Family ListBox is clicked. It will clear the Module Types List Fill Range and repopulate it with all Module Types from the newly selected Device Family.

Private Sub ListBox2_Click()
Dim row As Integer
row = 2
Dim totalRows As Integer
Dim ModuleTypes(30) As Variant
Dim ArrayIndex As Integer
ArrayIndex = 0
totalRows = ThisWorkbook.Sheets("Tables").Cells(2, 5).Value

'Clear the Module Type selection list on the PLC Module Data Sheet.
'ThisWorkbook.Sheets("PLC Module Data").Range("C2:C500").Clear

'Clear the Table Name search result list on the PLC Module Data Sheet.
ThisWorkbook.Sheets("PLC Module Data").Range("I2:L500").Clear 

'Search the Table Name sheet for all entries fromm the selected MFG and with the selected Family and paste them onto the PLC Module Data Sheet.
For i = 2 To totalRows
    If (ThisWorkbook.Sheets("Tables").Cells(i, 2).Value = ListBox1.Value) And (ThisWorkbook.Sheets("Tables").Cells(i, 3).Value = ListBox2.Value) Then
    ThisWorkbook.Sheets("PLC Module Data").Cells(row, 9).Value = ThisWorkbook.Sheets("Tables").Cells(i, 1).Value
    ThisWorkbook.Sheets("PLC Module Data").Cells(row, 10).Value = ThisWorkbook.Sheets("Tables").Cells(i, 2).Value
    ThisWorkbook.Sheets("PLC Module Data").Cells(row, 11).Value = ThisWorkbook.Sheets("Tables").Cells(i, 3).Value
    ThisWorkbook.Sheets("PLC Module Data").Cells(row, 12).Value = ThisWorkbook.Sheets("Tables").Cells(i, 4).Value
    row = row + 1
End If
Next I

'Form an array of all unique Module Types within the Table search results list.
For i = 2 To ThisWorkbook.Sheets("PLC Module Data").Cells(2, 13).Value
If ArrayTest(ModuleTypes, ThisWorkbook.Sheets("PLC Module Data").Cells(i, 12).Value) Then
    ModuleTypes(ArrayIndex) = ThisWorkbook.Sheets("PLC Module Data").Cells(i, 12).Value
    ArrayIndex = ArrayIndex + 1
End If
Next i

'Copy the ModuleTypes Array into the Module Type selection list on the PLC Module Data Sheet.
For i = 0 To UBound(ModuleTypes)
ThisWorkbook.Sheets("PLC Module Data").Cells(2 + i, 3).Value = ModuleTypes(i)
Next i

'Clear the Temp search result list on the PLC Module Data Sheet.
ThisWorkbook.Sheets("PLC Module Data").Range("U2:X500").Clear

If anyone knows how I can get around the issues I listed above and get the set of ListBoxes to work seamlessly together, it would be very much appreciated. thanks.

Solution

Thanks for the feedback, everyone. I got them working using the flag method mentioned in the comments.