Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of listbox to listbox code
apologise this is reply to another messsage.
R.VENKATARAMAN wrote in message ... see help under do while do until ============= Sam S via OfficeKB.com wrote in message ... I have a sheet with product item numbers in column B and product names in column C and each of these products has a category name associated with it in column A on the same row. There are several hundred products but only 20 different categories. In a lisbox - How do I display only one entry for each category (20) and if one of these catagories is selected in the list box then [only] the products (Item Number and Product name) corresponding to the that selected category are displayed in another listbox? ********* The code below works but only fills listbox2 with 1 column of values - how do I modify this code to fill listbox2 with 2 columns of data instead of 1 (would also like listbox2 data sorted) Thank you, Sam Private Sub ListBox1_Change() Dim a(), i As Long, r As Range With Sheets("sheet1") For Each r In .Range("a1", .Range("a65536").End(xlUp)) If r = Me.ListBox1.Value Then ReDim Preserve a(i) a(i) = r.Offset(, 1).Value: i = i + 1 End If Next End With With Me.ListBox2 .Clear .List() = a End With Erase a: i = 0 End Sub Private Sub UserForm_Initialize() Dim dic As Object, x, r As Range Set dic = CreateObject("Scripting.Dictionary") With Sheets("sheet1") For Each r In .Range("a1", .Range("a65536").End(xlUp)) If Not IsEmpty(r) And Not dic.exists(r.Value) Then dic.Add r.Value, Nothing End If Next End With x = dic.keys: Set dic = Nothing Me.ListBox1.List() = x End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
ListBox List Code? | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |