Userform - ListBox items added based on Combobox value selected

Hello Everyone,

I am trying to make a userform to show a list of items that each person is holding in their "account". I have chosen to use a combobox to select the Account name, this is done.

I am having trouble with the listbox to show the list of items though. What would be needed is very similar to vlookup I think because the macro would take the name from combobox1, which is taken from a list on a sheet called "deposits", columnA. Listbox1 would simply need to look down columnA and show the item name, from columnC for each item with that account name.

Column A and Column C would go down to row 23000 and there is no limit to the number of items that can be attached to the same account - just in case that matters.

If any more information is needed, I'd be happy to provide it. Many thanks in advance

Ok. I have tested it out and it doesn't work. I think it's something I'm doing rather than a fault in the code.

Where do i put the code in order to make it trigger and the right time? I tried it in ComboBox1 change, and click because i'm expecting that the listbox is to be filled when the combobox is changed by the user.

Also, what does the range D1 do? in range d1 there isn't any relevant information on my sheet?

Hi Tinbendr,

I actually do need the code to lookup more than one item. For each time the account name in ColA matches the one in ComboBox1, give the value of colC.

I will try your method mikerickson and report back.

Thank you Mehmetcik, this is getting very close to what I am looking to do.

Your code for the combobox change event is something I've never seen so I don't know where to begin altering it to look horizontally across the row, instead of looking for the header that matches Cbox1. Can you perhaps shed any light on that?

Thank you either way, I'm still learning when it comes to VBA and what you've given me there is very interesting

I feel i need to clarify things.

I'm trying to make it so that if someone selects "John Smith" from the dropdown in combobox1 (which is populated from all unique names in colA), they are presented with a list of items that appear next to that name. So....

colA colC
John Smith Item1
Sarah Jane Item2
Jim Beam Item3
John Smith Item4

If john smith is selected, then listbox1 would be populated by Item1 and Item4.

Post a sample so I can see your data structure

Here's a sample file. You'll notice that the names I am using to populate combobox1 are actually just "=ColF". This is because of the way that the data in the list is being pasted in, I don't think that any code can take values in ColF and look backwards along a row - though i may be wrong.

I have actually provided the attachment with a simple userform that shows the code i'm currently using in it (sorry, i forgot to make a button to run it so you will have to open VisBasic to see it i'm afraid.

I think the existing code may be close, because when user selects "anonymous" the listbox develops a scroll bar, indicating to me that it is finding more rows of blank cells than it can show in the space it has.

Just so you know, all I need to show in the listbox is a list of ColC values attributed to the selected name, not any of the other information

Thanks again.

Ok try this version:

What you've given me there works well, I did have to tweak it slightly though as the names were just for example and your code told the combobox to start with the name "Anonymous" showing. I have altered the code to look at F27 so that the word "Name" becomes that first entry.

I'm puzzled though, because I didn't touch some of the code that refers to "anonymous" in the userform initialize sub, and even when i changed the name anonymous in my table to something else, there was no error - what is

 If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))

doing?

Private Sub Combobox1_Change()

'Find All Matching Entries
   
    ListBox1.Clear ' added this to clear listbox when new name selected
    Set rngLook = Range("F27:F" & Range("A40000").End(xlUp).Row) 'changed to F27
    strValueToPick = ComboBox1.Value
    With rngLook
        Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = rngFind
            Do
                Set rngPicked = Union(rngPicked, rngFind)
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
    
    If Not rngPicked Is Nothing Then
        rngPicked.Offset(0, -3).Select
        
        'Fill Listbox
        For Each c In Selection
        ListBox1.AddItem c.Value
        Next
        
    End If
    
End Sub

Private Sub UserForm_Activate()

    Dim UniqueItem As Collection, cell As Range, N As Long
    
'Read your list of names
MyArray = Range("F27:F" & Range("A40000").End(xlUp).Row).Value ' changed to F27
'Remove Duplicates

    
    Set UniqueItem = New Collection
    
    UniqueItem.Add "Name", "Name" ' Changed from Anonymous to Name
    
    On Error Resume Next
    
    For Count = 1 To UBound(MyArray)
    
    If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))
    Next
    On Error GoTo 0

For Count = 1 To UniqueItem.Count

    ComboBox1.AddItem UniqueItem(Count)
    Next

ComboBox1.ListIndex = 0


End Sub

I am ditching the name anonymous because I want that as the first entry
modify it to:-

UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))

If you are using name as your first entry

Sorry I don't know.

Look at the bottom of your post