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


---
Regards,
Norman



"Sam S via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> 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

[R.VENKATARAMAN]

see help under
do while
do until
=============
Sam S via OfficeKB.com <[email protected]> wrote in message
news:[email protected]...
>
> 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

[R.VENKATARAMAN]

apologise this is reply to another messsage.


R.VENKATARAMAN <vram26@vsnl$$$.net> wrote in message
news:#[email protected]...
> see help under
> do while
> do until
> =============
> Sam S via OfficeKB.com <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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

Norman Jones wrote:
>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


--
Message posted via http://www.officekb.com