Excel User Form_ List Box Macro

[Shantanu_4612]

Hi All,

I need you help with the Listbox in Userform,

I have two columns in Excel Raw file,

Column A has got categories and B has got the unique data.

I want to have all the Data from Column B in the list box, which falls in the category (From Column A) mentioned in TextBox1.

kindly assist

Thank You

 

[CalcSux78]

You could create a loop to search for matches within column A and load the listbox with values from the corresponding match from column B.

Here's a great description of listboxes and how they work: http://www.globaliconnect.com/excel...roperty-listindex-rowsource-selected-property

Example using the TextBox Change event:

Option Explicit
Option Compare Text     'case insensitive matches allowed


Private Sub TextBox1_Change()
Dim rng As Range
Dim aCat() As Variant
Dim i&


Set rng = Range([A2], Cells(Rows.Count, 1).End(xlUp).Offset(, 1))


aCat = rng  'assign rng to the array


ListBox1.Clear
For i = LBound(aCat, 1) To UBound(aCat, 1)
    If aCat(i, 1) = TextBox1 Then ListBox1.AddItem aCat(i, 2)
Next i


End Sub

 

[Norie]

You could use a dictionary for this see here UFComboListDicEx.

 

[Shantanu_4612]

hi CalcSux78,

With the script that you have shared,

I am getting an Application defined or object defined error.

plus I am not able to under how will it pick data from Column B?

I have categories in Column A and Data in Column B

Please assist.

 

[Shantanu_4612]

Hi,

Not able to access the file.

 

[Norie]

What happens when you click the link?

 

[CalcSux78]

Shantanu,

The first error could be tied back to the Option Explicit at the top of the code in post#2. With Option Explicit, all variables used within each sub of that module must be declared. (If you're still getting errors after cleaning up declarations, post back which line the code halts on)

As far as setting up the range for the sub, I set it up to use the range between A2 and the cell in column B where the last value in column A is found. Once the range is set, I sent the range to an array to improve processing speed.

 

[Norie]

Here's the main code from the workbook in the link I posted.

Option Explicit
Dim dicCat As Object

Function CreateDic() As Object
Dim dic As Object
Dim arr()
Dim cnt As Long
Dim rng As Range

    Set dic = CreateObject("Scripting.Dictionary")
    
    
    Set rng = Range("A2")
    
    Do
        If dic.exists(rng.Value) Then
            arr = dic(rng.Value)
            
            ReDim Preserve arr(LBound(arr) To UBound(arr) + 1)
            
            arr(UBound(arr)) = rng.Offset(, 1).Value
            
            dic(rng.Value) = arr
        Else
            dic.Add rng.Value, Array(rng.Offset(, 1).Value)
        End If
        
        Set rng = rng.Offset(1)
    Loop Until rng.Value = ""
    
    Set CreateDic = dic
End Function

Private Sub ComboBox1_Change()
Dim idx As Long

    idx = Me.ComboBox1.ListIndex
    
    If idx = -1 Then Exit Sub
    
    Me.ListBox1.List = dicCat(Me.ComboBox1.List(idx))
    
End Sub

Private Sub UserForm_Initialize()

Dim ky

    Set dicCat = CreateDic

    
    For Each ky In dicCat.keys
    
        Me.ComboBox1.AddItem ky
        
    Next ky
    
End Sub

This code would go in the module of a userform which has a combobox called ComboBox1 and a listbox called ListBox1.

In Sheet1, or the active sheet column A would be populated with categories, in my example Category1-5 randomly distributed, and column B would have the items, Item1-Item20.

Note, I used a combobox and listbox to make it a little easier to visually check the code worked - it would be straightforward to change from a listbox to a combobox.

 

[Shantanu_4612]

Thanks a lot for the explaination Brother,

I used your logic with some of mine and bingo I got the solution..

Thanks a lot