UserForm ListBoxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tgilmour - 28 Jul 2007 18:04 GMT

I have a userform based on the "Cascading ListBoxes" example from Greg
Maxey's site http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm.

It populates list boxes dependent on the value of the prior list box.
How do I extend this functionality beyond 3 list boxes? For example,
how would I add the color (List Box 4) and cost (list box 5) of the
selected model?

ListBox1 Data (Manufacturer)
Dell (row 1)
HewlettPackard (row 2)

ListBox 2 Data (Category)
Desktop ¶ Notebook (row 1)
Desktop ¶ Notebook (row 2)

ListBox 3 Data (Model)
Dimension E520 | Dimension E521¶ Inspiron 6400 | Inspiron 9400 (row 1)
Pavillion d4650 | Pavillion a1650¶ Pavillion dv9000 | Pavillion dv6000
(row 2)

ListBox 4 (color)
Blue | Silver | Red ¶ Black | Brown | Grey (row 1)
Orange | Green ¶ White | Yellow (row 2)

ListBox 5 (cost)
$999 ¶ $1199 (row 1)
$1000 ¶ $1200 (row 2)

Thanks in advance for your help.

Here is the code.

Option Explicit
Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(FileName:="M:\Data.doc",
Visible:=False)
i = sourcedoc.Tables(1).Rows.Count - 1
j = sourcedoc.Tables(1).Columns.Count
ListBox1.ColumnCount = j
'Hide columns 2 and 3
ListBox1.ColumnWidths = "75;0;0"
ReDim myArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
myArray(m, n) = myitem.Text
Next m
Next n
'Load data into ListBox1
ListBox1.List() = myArray
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub ListBox1_Change()
Dim myArray As Variant
myArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13))
ListBox2.List = myArray
ListBox3.Clear
End Sub

Private Sub ListBox2_Change()
Dim myArray1 As Variant
Dim myArray2 As Variant
myArray1 = Split(ListBox1.List(ListBox1.ListIndex, 2), Chr(13))
myArray2 = Split(myArray1(ListBox2.ListIndex), "|")
ListBox3.List = myArray2
End Sub

Reply to this Message

Doug Robbins - Word MVP - 29 Jul 2007 11:25 GMT

Have your data in this form

Table 1 Manufacturers

Manufacturer ManufacturerID
Dell 1
HP 2

Table 2 Categories

ManufacturerID Category CategoryID
1 Desktop¶ 1¶
Notebook 2
2 Desktop¶ 3¶
Notebook 4

Table 3 Models

Category ID Model ModelID
1 Dimension E520¶ 1¶
Dimension E521 2¶
2 Inspiron 6400¶ 3¶
Inspiron 9400 4¶
3 Pavillion d4650¶ 5¶
Pavillion a1650 6¶
4 Pavillion dv9000¶ 7¶
Pavillion dv6000 8

Table 4 Colour

Model ID Colour ColourID
1 Blue¶ 1¶
Silver¶ 2¶
Red¶ 3¶
Black¶ 4¶
Brown¶ 5¶
Grey 6

2 Blue¶ 7¶
Silver¶ 8¶
Red¶ 9¶
Black¶ 10¶
Brown¶ 11¶
Grey 12
3 Blue¶ 13¶
Silver¶ 14¶
Red¶ 15¶
Black¶ 16¶
Brown¶ 17¶
Grey 18

etc

Table 5 price

ColourID Price
1 999¶
1,199
2 1,000¶
1,200
3 etc.

etc For as many cascades as you want.

Then on the userform, use two column listboxes with the item manufacturer
and and manufacturerID in the columns of the first list box, the Catergory
and the CategoryID in the columns of the second listbox, the Model and
ModelID in those of the third, the Colour and the ColourID in those of the
fourth, etc, etc.

Then you use the BoundColumn attribute of the ListBox to get the ID of the
item that is selected and then load the subsequent list box with the items
corresponding to that ID.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Reply to this Message

tgilmour - 30 Jul 2007 00:14 GMT

Thank you for responding. Is there a limitation to Greg Maxey's
method, the way I was trying? Can you direct me to the code for the
cascading two column listbox method?

Reply to this Message

Doug Robbins - Word MVP - 30 Jul 2007 06:36 GMT

The following is not doing the same thing, but uses the commands that you
would be needing:

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thank you for responding. Is there a limitation to Greg Maxey's
> method, the way I was trying? Can you direct me to the code for the
> cascading two column listbox method?

Reply to this Message

tgilmour - 14 Aug 2007 23:35 GMT

Ok, I'm back on this project. Does anyone have an example of how to
do this????

"Then on the userform, use two column listboxes with the item
manufacturer
and and manufacturerID in the columns of the first list box, the
Catergory
and the CategoryID in the columns of the second listbox, the Model
and
ModelID in those of the third, the Colour and the ColourID in those of
the
fourth, etc, etc.

Then you use the BoundColumn attribute of the ListBox to get the ID of
the
item that is selected and then load the subsequent list box with the
items
corresponding to that ID. "

Reply to this Message

Doug Robbins - Word MVP - 15 Aug 2007 00:32 GMT

See the Cascading Listboxes section of the
following page on fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Ok, I'm back on this project. Does anyone have an example of how to
> do this????
[quoted text clipped - 14 lines]
> items
> corresponding to that ID. "

Reply to this Message

tgilmour - 15 Aug 2007 02:32 GMT

Thanks for the response but my original question was how can I extend
the functionality of Greg's cascading listboxes beyond 3 list boxes?
I hate to be a pain and appreciate any help.

I have a userform based on the "Cascading ListBoxes" example from Greg
Maxey's site http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm.

It populates list boxes dependent on the value of the prior list box.
How do I extend this functionality beyond 3 list boxes? For example,
how would I add the color (List Box 4) and cost (list box 5) of the
selected model?

Thanks...

Reply to this Message

Doug Robbins - Word MVP - 15 Aug 2007 09:36 GMT

Going back to the information that I posted on 29 July which I have modified
slightly below

When a selection is made in the Manufacturer's listbox (or combobox), you
use Change event of the combobox to set the BoundColumn attribute of that
combobox to 2, to get the ID assigned to the Manufacturer

Dim ManufacturerID as Long

cmbManufacturer BoundColumn = 2
ManufacturerID = cmbManufacturer

The variable ManufacturerID will now contain the row number of the row
containing the data that you want to load into the next combobox.

For example, if you had picked Dell, you would load the information from
columns 2 and 3 of row 2 of the Categories table

Have your data in this form

Table 1 Manufacturers

Manufacturer ManufacturerID
Dell 2
HP 3

Table 2 Categories

ManufacturerID Category CategoryID
2 Desktop¶ 2¶
Notebook 3
3 Desktop¶ 4¶
Notebook 5

Table 3 Models

Category ID Model ModelID
2 Dimension E520¶ 2¶
Dimension E521 3¶
3 Inspiron 6400¶ 4¶
Inspiron 9400 5¶
4 Pavillion d4650¶ 6¶
Pavillion a1650 7¶
5 Pavillion dv9000¶ 8¶
Pavillion dv6000 9

Table 4 Colour

Model ID Colour ColourID
2 Blue¶ 2¶
Silver¶ 3¶
Red¶ 4¶
Black¶ 5¶
Brown¶ 6¶
Grey 7

3 Blue¶ 8¶
Silver¶ 9¶
Red¶ 10¶
Black¶ 11¶
Brown¶ 12¶
Grey 13
4 Blue¶ 14¶
Silver¶ 15¶
Red¶ 16¶
Black¶ 17¶
Brown¶ 18¶
Grey 19

etc

Table 5 price

ColourID Price
2 999¶
1,199
3 1,000¶
1,200
4 etc.

etc For as many cascades as you want.

Then on the userform, use two column listboxes with the item manufacturer
and and manufacturerID in the columns of the first list box, the Catergory
and the CategoryID in the columns of the second listbox, the Model and
ModelID in those of the third, the Colour and the ColourID in those of the
fourth, etc, etc.

Then you use the BoundColumn attribute of the ListBox to get the ID of the
item that is selected and then load the subsequent list box with the items
corresponding to that ID

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thanks for the response but my original question was how can I extend
> the functionality of Greg's cascading listboxes beyond 3 list boxes?
[quoted text clipped - 9 lines]
>
> Thanks...

Reply to this Message

Doug Robbins - Word MVP - 15 Aug 2007 09:43 GMT

Also see
http://groups.google.com/group/microsoft.public.word.vba.userforms/browse_thread
/thread/f3f67faa8297aa66/bf86a5657fe2c34e?q=doug's+combo+box+code&rnum=1#bf86a56
57fe2c34e

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thanks for the response but my original question was how can I extend
> the functionality of Greg's cascading listboxes beyond 3 list boxes?
[quoted text clipped - 9 lines]
>
> Thanks...

Reply to this Message