Adding items to a listbox from a selection in another listbox in excel vba

Question

What I'm looking to accomplish is to have a selection from a listbox (SelectHousingList) filter through a table in excel and come back with values in the same row but different column (much like the index/vlookup). Then display a list of choices with the same value in the first row and show the values in the second listbox (CatalystTypeList).

My code so far is as follows but nothing is showing up in the second listbox. I'm getting no error messages.

The first listbox has 9 values in the rows so I'm trying to use the listindex function in vb to lookup that value in the table in excel.

CatalystTypeList.Clear

lastrow1 = Sheet3.Cells(Rows.Count, 28).End(xlUp).Row

curVal = SelectHousingList.List(SelectHousingList.ListIndex, 6)

Dim dict2 As Object
Set dict2 = CreateObject("Scripting.Dictionary")
For x = 29 To lastrow1
If Worksheets("Catalyst 2020A1").Cells(x, "D") = curVal Then
    If Not dict.Exists(Worksheets("Catalyst 2020A1").Cells(x, "A").Value) Then
        Me.CatalystTypeList.AddItem Worksheets("Catalyst 2020A1").Cells(x, "A")
        dict(Worksheets("Catalyst 2020A1").Cells(x, "A").Value) = 1
    End If
End If
Next x

Have you tried stepping through the code? That may show the issue. — Brian M Stafford
– Brian M Stafford, Commented Mar 17, 2020 at 13:29
I tried to but I'm struggling to get the step through all the way to this point since I have a lot of code before this. I begin but it ends up not making it to the private sub this is in. — JoshL
– JoshL, Commented Mar 17, 2020 at 13:35
Set a break point in this sub instead of trying to step into it. If you still don't get here, then this code likely isn't the issue. — Brian M Stafford
– Brian M Stafford, Commented Mar 17, 2020 at 13:40
Thank you for that, I was able to step through this code and it is continuously jumping from the if worksheets to the End If without going through the middle so I'll take a look there. — JoshL
– JoshL, Commented Mar 17, 2020 at 13:44
Is curVal correct. Maybe add .value to the checking range. — Nathan_Sav
– Nathan_Sav, Commented Mar 17, 2020 at 14:05

Dharman · Accepted Answer · 2020-03-17 15:22:36Z

1

I figured out with the help of all the comments how to use break points to help with stepping through and easier for finding errors in my code.

Here's the code I have in the end (I left the lines I took out as comments so everyone could see what I changed).

CatalystTypeList.Clear

lastrow1 = Worksheets("Catalyst 2020A1").Cells(Rows.Count, 28).End(xlUp).Row

curVal = Worksheets("Catalyst 2020A1").Range("D26").Value   
'SelectHousingList.List(SelectHousingList.ListIndex, 6)

Dim dict2 As Object
Set dict2 = CreateObject("Scripting.Dictionary")
For x = 29 To lastrow1
If Worksheets("Catalyst 2020A1").Cells(x, "D") = curVal Then
    'If Not dict.Exists(Worksheets("Catalyst 2020A1").Cells(x, "A").Value) Then
        Me.CatalystTypeList.AddItem Worksheets("Catalyst 2020A1").Cells(x, "A")
        'dict(Worksheets("Catalyst 2020A1").Cells(x, "A").Value) = 1
    End If
'End If
Next x

edited Mar 17, 2020 at 15:22

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Mar 17, 2020 at 14:48

JoshL

1641 gold badge2 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding items to a listbox from a selection in another listbox in excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related