www.officekb.com

8/3/2019 Www.officekb.com

1/8

MS Office Forum /Word /Programming /August 2007

Tip: Looking for answers? Try searching our database.

cascading list box userform Search

UserForm ListBoxes

Thread view: Tree 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 GregMaxey'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 theselected 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 ExplicitPrivate Sub UserForm_Initialize()Dim myArray() As VariantDim sourcedoc As DocumentDim i As IntegerDim j As IntegerDim myitem As RangeDim m As LongDim n As LongApplication.ScreenUpdating = FalseSet sourcedoc = Documents.Open(FileName:="M:\Data.doc",Visible:=False)i = sourcedoc.Tables(1).Rows.Count - 1j = sourcedoc.Tables(1).Columns.CountListBox1.ColumnCount = j

Page 1 of 8UserForm ListBoxes

11/23/2011http://www.officekb.com/Uwe/Forum.aspx/word-vba/17064/UserForm-ListBoxes


2/8

'Hide columns 2 and 3ListBox1.ColumnWidths = "75;0;0"ReDim myArray(i, j)For n = 0 To j - 1For m = 0 To i - 1

Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Rangemyitem.End = myitem.End - 1myArray(m, n) = myitem.Text

Next mNext n'Load data into ListBox1ListBox1.List() = myArraysourcedoc.Close SaveChanges:=wdDoNotSaveChangesEnd Sub

Private Sub ListBox1_Change()Dim myArray As VariantmyArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13))ListBox2.List = myArrayListBox3.ClearEnd Sub

Private Sub ListBox2_Change()Dim myArray1 As VariantDim myArray2 As VariantmyArray1 = Split(ListBox1.List(ListBox1.ListIndex, 2), Chr(13))myArray2 = Split(myArray1(ListBox2.ListIndex), "|")ListBox3.List = myArray2End 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 ManufacturerIDDell 1HP 2

Table 2 Categories

ManufacturerID Category CategoryID1 Desktop 1

Notebook 22 Desktop 3

Notebook 4

Table 3 Models

Category ID Model ModelID1 Dimension E520 1

Dimension E521 22 Inspiron 6400 3

Inspiron 9400 43 Pavillion d4650 5

Pavillion a1650 64 Pavillion dv9000 7

Pavillion dv6000 8

Table 4 Colour

Model ID Colour ColourID




3/8

1 Blue 1Silver 2Red 3Black 4Brown 5Grey 6

2 Blue 7

Silver 8Red 9Black 10Brown 11Grey 12

3 Blue 13Silver 14Red 15Black 16Brown 17Grey 18

etc

Table 5 price

ColourID Price1 999

1,1992 1,000

1,2003 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 Catergoryand the CategoryID in the columns of the second listbox, the Model andModelID in those of the third, the Colour and the ColourID in those of thefourth, etc, etc.

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

Signature


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 theselected 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




4/8

(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 ExplicitPrivate Sub UserForm_Initialize()Dim myArray() As VariantDim sourcedoc As DocumentDim i As IntegerDim j As IntegerDim myitem As Range

Dim m As LongDim n As LongApplication.ScreenUpdating = FalseSet sourcedoc = Documents.Open(FileName:="M:\Data.doc",Visible:=False)i = sourcedoc.Tables(1).Rows.Count - 1j = sourcedoc.Tables(1).Columns.CountListBox1.ColumnCount = j'Hide columns 2 and 3ListBox1.ColumnWidths = "75;0;0"ReDim myArray(i, j)For n = 0 To j - 1For m = 0 To i - 1

Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Rangemyitem.End = myitem.End - 1myArray(m, n) = myitem.Text

Next mNext n'Load data into ListBox1ListBox1.List() = myArraysourcedoc.Close SaveChanges:=wdDoNotSaveChangesEnd Sub

Private Sub ListBox1_Change()Dim myArray As VariantmyArray = Split(ListBox1.List(ListBox1.ListIndex, 1), Chr(13))

ListBox2.List = myArrayListBox3.ClearEnd Sub

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


tgilmour - 30 Jul 2007 00:14 GMT




5/8

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


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 aseparatedocument (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() andthe 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.docApplication.ScreenUpdating = False' Open the file containing the client detailsSet sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")' Get the number or clients = number of rows in the table of client

details less onei = sourcedoc.Tables(1).Rows.Count - 1' Get the number of columns in the table of client detailsj = 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 dataDim MyArray() As Variant'Load client data into MyArrayReDim MyArray(i, j)For n = 0 To j - 1

For m = 0 To i - 1Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Rangemyitem.End = myitem.End - 1MyArray(m, n) = myitem.Text

Next mNext n' Load data into ListBox1

ListBox1.List() = MyArray' Close the file containing the client detailssourcedoc.Close SaveChanges:=wdDoNotSaveChanges

End Sub

Private Sub CommandButton1_Click()Dim i As Integer, Addressee As StringAddressee = ""For i = 1 To ListBox1.ColumnCount

ListBox1.BoundColumn = iAddressee = Addressee & ListBox1.Value & vbCr

Next iActiveDocument.Bookmarks("Addressee").Range.InsertAfter AddresseeUserForm2.HideEnd Sub

The Initialize statement will populate the listbox with the data from the




6/8

table and then when a client is selected in from the list and the commandbutton is clicked, the information for that client will be inserted into abookmark in the document. You may want to vary the manner in which it isinserted to suit our exact requirements, but hopefully this will get youstarted.

Signature

> 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?


tgilmour - 14 Aug 2007 23:35 GMT

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

"Then on the userform, use two column listboxes with the itemmanufacturerand and manufacturerID in the columns of the first list box, the

Catergoryand the CategoryID in the columns of the second listbox, the ModelandModelID in those of the third, the Colour and the ColourID in those ofthefourth, etc, etc.

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


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

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

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

Signature

> 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. "


tgilmour - 15 Aug 2007 02:32 GMT

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


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 theselected model?




7/8

Thanks...



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

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

Dim ManufacturerID as Long

cmbManufacturer BoundColumn = 2ManufacturerID = cmbManufacturer

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

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

Have your data in this form

Table 1 Manufacturers

Manufacturer ManufacturerIDDell 2HP 3

Table 2 Categories

ManufacturerID Category CategoryID2 Desktop 2Notebook 3

3 Desktop 4Notebook 5

Table 3 Models

Category ID Model ModelID2 Dimension E520 2

Dimension E521 33 Inspiron 6400 4

Inspiron 9400 5

4 Pavillion d4650 6Pavillion a1650 75 Pavillion dv9000 8

Pavillion dv6000 9

Table 4 Colour

Model ID Colour ColourID2 Blue 2

Silver 3Red 4Black 5Brown 6Grey 7

3 Blue 8Silver 9




8/8

Red 10Black 11Brown 12Grey 13

4 Blue 14Silver 15Red 16Black 17

Brown 18Grey 19

etc

Table 5 price

ColourID Price2 999

1,1993 1,000

1,2004 etc.

etc For as many cascades as you want.

Then on the userform, use two column listboxes with the item manufacturerand and manufacturerID in the columns of the first list box, the Catergoryand the CategoryID in the columns of the second listbox, the Model andModelID in those of the third, the Colour and the ColourID in those of thefourth, etc, etc.

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

Signature

> 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...



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

Signature

> 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...


Find more on: cascading list box userform

2011 Advenet LLC Privacy Policy - Terms of UseThis website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.
