Re: Populate a Multi-Column ListBox

Shauna,

I am passed disillusioned ;-). I suppose I am just looking for
confirmation from others like you, that I have tried all the trails and
experience all the errors before I give up on Variant = X = column of
values.

Perhaps I have. Thanks.

Shauna Kelly wrote:
Greg

I don't want to disillusion you, but there's a great deal in the help files
that is either not true, is true in VB6 but not in VBA, is true in VBA in
other apps but not in Word, or is true in some circumstances in Word and not
in other circumstances.

Only trial and error can distinguish these cases.

Shauna

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word


"Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
news:u$P9be7OHHA.2312@xxxxxxxxxxxxxxxxxxxxxxx
Doug,

I must seem particularily thick to you at this moment, but while what you
are saying makes sense in a way, it still doesn't explain the Help file
statement that the Variant can be used to specify a column of values:
Variant Optional. Specifies a single value, a column of values, or a
two-dimensional array to load into a ListBox or ComboBox.

The below code first uses AddItem to create three rows and populate them
with the contents of myArray 1. Now the spaces exists so it seems I
should be able to repopulate "a column of values" and fill the existing
column of values with myArray2 using the last statement below. That code
generates an error "Could not set the column property. Invalid property
array index."

If the Help file is correct, then something "X" has to fit the equation
"Variant = X = a column of values" If myArray2 can't be X, then what
can?


Private Sub UserForm_Initialize()
Dim myArray1 As Variant
Dim myArray2 As Variant
Dim i As Long
myArray1 = Split("A|B|C", "|")
myArray2 = Split("1|2|3", "|")
For i = 0 To UBound(myArray1)
ListBox1.AddItem myArray1(i)
Next i
ListBox1.Column(1) = myArray2
End Sub

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Doug Robbins - Word MVP wrote:
But, first you have to use AddItem to create the "spaces" in the
ListBox
This is from the Help on AddItem:

Quote

For a multicolumn ListBox or ComboBox, AddItem inserts an entire row,
that is, it inserts an item for each column of the control. To assign
values to an item beyond the first column, use the List or Column
property and specify the row and column of the item.

Unquote

If you populate a ListBox with an Array using the List of Column
properties, the List the number of "spaces" in the list is equal to
the dimension of the Array. Thus a Two dimensional Array will create
and populate the "spaces" in two columns of a ListBox. Any columns
in the Listbox (whether set in the Properties window or by code) in
excess of the dimension of the Array are essentially locked and
cannot be populated by using the List property and attempting to
specify the Index of the column. Likewise, additional Rows cannot be
add via the Column property.
You can however use the List and Column properties to populate, or
change the value of a item that occupies a "space" in the list box by
specifying the coordinates of that "space". But you cannot use them
to populate a "space" that does not exist following the prior use of
the List or Column property.


"Greg Maxey" <gmaxey@xxxxxxxx> wrote in message
news:1169154880.537214.47000@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Doug,

That doesn't hardly clear the fog ;-). I understand and have
experimented with the effect of loading a multicolumn listbox with a
two dimensional array.

However, isn't myArray1 a one dimensional array. It is just a list
of values. The Help file says that the variant can be used to
specify a single value, a column of values or two dimensional array.

What is the "Variant" that would be used to spectify a "column of
values?" I.e., fill column 1 with "this variant" list of values?



Doug Robbins - Word MVP wrote:
This is the relevant part of the VBA Help file, but note the Note:
Quote

You can also use Column to copy an entire two-dimensional array of
values to
a control. This syntax lets you quickly load a list of choices
rather than
individually loading each element of the list using AddItem.

Note When copying data from a two-dimensional array, Column
transposes the
contents of the array in the control so that the contents of
ListBox1.Column(X, Y) is the same as MyArray(Y, X). You can also
use List to
copy an array without transposing it.

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

"Greg Maxey" <gmaxey@xxxxxxxx> wrote in message
news:1169152240.262100.316700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks Doug. That is clearly more straightforward than my
attempt. I am still perplexed by the help file statement about
the .Column Property Variant Part:

Variant Optional. Specifies a single value, a column of values, or
a two-dimensional array to load into a ListBox or ComboBox.

That seems to be saying that there should be some direct means of
writing a "column of values" to a ListBox. Intuitively it would
appear to be something like:

ListBox1.Column(1) = myArray1
ListBox2.Column(2) = myArray2

But that doesn't work.


Doug Robbins - Word MVP wrote:
Hi Greg,

You have to do it as follows:

For i = 0 To UBound(myArray1)
ListBox1.AddItem
ListBox1.List(i, 0) = myArray1(i)
ListBox1.List(i, 1) = myArray2(i)
Next i


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

"Greg Maxey" <gmaxey@xxxxxxxx> wrote in message
news:1169128708.377992.233940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Doug,

Yep I agree that it may be a daft idea, but as I said it is a
practical
exericise and the VB Help clearly indicates to me at least the I
should
be able to write or read a "column of values" using the .Column
property.

Here is an example of what I am trying to demonstrate. I want to
populate a 2 column listbox with no external source using just
the initialize event. For example, say I wanted to list the
U.S. state names and then their abbreviations "hidden" in column
2. When the user
selects a state name, the userform inserts the abbreviation for
that state in the document.

It seems to me that I should somehow be able to directly populate
ListBox column 1 with an array of state names created using the
Split
method (I can) and then populate Listbox column 2 with an array
of abbreviations created using the Split method (I can't).

I can do that by first creating an array with the split funtion
of the
state names. Then create another array using the split function
of the
abbreviations, then a third two dimensional array using the first
two.
I can then populate the listbox using the third array.

Private Sub UserForm_Initialize()
Dim myArray1 As Variant
Dim myArray2 As Variant
Dim myArray3() As String
Dim i As Long
Dim oCount As Long
myArray1 = Split("Alabama|Alaska|Arizona|Etc.", "|")
myArray2 = Split("AL|AK|AZ|Etc", "|")
oCount = UBound(myArray1)
ReDim myArray3(oCount, 1)
For i = 0 To UBound(myArray1)
myArray3(i, 0) = myArray1(i)
Next i
For i = 0 To UBound(myArray2)
myArray3(i, 1) = myArray2(i)
Next i
ListBox1.ColumnCount = 2
ListBox1.ColumnWidths = "60;0"
ListBox1.List = myArray3
End Sub


Private Sub CommandButton1_Click()
Dim oRng As Word.Range
Me.ListBox1.TextColumn = 2
Set oRng = ActiveDocument.Bookmarks("State").Range
oRng.Text = Me.ListBox1.Text
ActiveDocument.Bookmarks.Add "State", oRng
Me.Hide
End Sub





Doug Robbins - Word MVP wrote:
Sorry Greg,

I think that is a daft idea. I do not consider a list box to be
appropriate
place to be linking items together. They should be linked at
their source.

If on the other hand you wanted the list box to display
non-contiguous
columns from the one data source, that can be accomplished by
setting
the
width of the intervening columns to zero.

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

"Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
news:ORA1geqOHHA.780@xxxxxxxxxxxxxxxxxxxxxxx
I know how to populate a multi-column listbox one item at a time
using
AddItem and List. I know how to populate a single column
ListBox using
List
and an array.

I am trying to figure out code that will populate column 1 of
of a two
colunm listobox with the contents of one array and column 2 with
the
contents of a second different array (I realize a better way
might be
to
populate the listbox with a multi-dimensional array but as a
practical
exercise I would like to work it out the way I described)

Here is the code I have put together.
Private Sub UserForm_Initialize()
Dim myArray As Variant
With ListBox1
.ColumnCount = 2
.AddItem "1"
.List(0, 1) = "A"
.AddItem "2"
.List(1, 1) = "B"
.AddItem "3"
.List(2, 1) = "C"
.AddItem "Etc."
.ColumnWidths = "90;90"
End With
With ListBox2
myArray = Split("1|2|3|4|5", "|")
.List = myArray
End With
'Code works to this point. Now I would like to make ListBox2 a
2 column
ListBox. Populate column 1 with the first myArray then populate
column 2
with the second.
'When I run the code below, nothing is placed in the second
column. Any
ideas?

With ListBox2
.ColumnCount = 2
.BoundColumn = 1
myArray = Split("1|2|3|4|5", "|")
.List = myArray
.BoundColumn = 2
myArray = Split("A|B|C|D|E", "|")
.List = myArray
End With
End Sub



--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.



.