Modification of listbox to listbox code

[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

 

[Norman Jones]

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

 

[R.VENKATARAMAN]

see help under
do while
do until
=============

 

[R.VENKATARAMAN]

apologise this is reply to another messsage.

see help under
do while
do until
=============

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

 

[Sam S via OfficeKB.com]

Thanks - I'll check both out today.

Sam

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

[quoted text clipped - 51 lines]

Me.ListBox1.List() = x
End Sub