Excel User Form_ List Box Macro

Shantanu_4612

New Member
Joined
Oct 3, 2016
Messages
27
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

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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/index.php?option=com_content&view=article&id=128:excel-userform-controls-combobox-and-listbox-additem-method-boundcolumn-list-property-listindex-rowsource-selected-property

Example using the TextBox Change event:
Code:
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
 

Shantanu_4612

New Member
Joined
Oct 3, 2016
Messages
27
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
What happens when you click the link?
 

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Here's the main code from the workbook in the link I posted.
Code:
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

New Member
Joined
Oct 3, 2016
Messages
27
Thanks a lot for the explaination Brother,

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

Thanks a lot
 

Forum statistics

Threads
1,082,271
Messages
5,364,153
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top