Modification of listbox to listbox code

  • Thread starter Thread starter Sam S via OfficeKB.com
  • Start date Start date
S

Sam S via OfficeKB.com

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
 
apologise this is reply to another messsage.


R.VENKATARAMAN said:
see help under
do while
do until
=============
Sam S via OfficeKB.com said:
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
Click to expand...
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 -
Click to expand...
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
Click to expand...
Click to expand...
 
Thanks - I'll check both out today.

Sam

Norman said:
Hi Sam,

See Debra Dalgleish's page on Dependent Lists at:

http://www.contextures.com/xlDataVal02.html

See also xlDynamic's page on Dependent Dropdowns at:

http://www.xldynamic.com/source/xld.Dropdowns.html

---
Regards,
Norman
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
Click to expand...
[quoted text clipped - 51 lines]
Me.ListBox1.List() = x
End Sub
Click to expand...
Click to expand...
 
Back
Top