Re: Populate a Multi-Column ListBox

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Greg,

I will confirm what Shauna says - Help is, at best, a guide who makes mistakes. From personal experience I can tell you that checking manuals and/or help files is not what developers are good at or enjoy, and mistakes get in there.

In this case the Help is wrong and should be bugged the same way we bug the software itself (whether MS would take it seriously is another question).

You cannot address a single Column of a Listbox. .Column (without any indexes) provides (or lets you set) the data for the whole two-dimensional Listbox array by Column - in the same way as .List provides it by Row; in Excel terms Column is List transposed. There is no one-dimensional access.

--
Enjoy,
Tony

"Greg Maxey" <gmaxey@xxxxxxxx> wrote in message news:1169209582.747175.274260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
>
>


.

Relevant Pages

  • Re: Populate a Multi-Column ListBox
    ... two-dimensional array to load into a ListBox or ComboBox. ... For a multicolumn ListBox or ComboBox, AddItem inserts an entire row, ... If you populate a ListBox with an Array using the List of Column ... Dim myArray As Variant ...
    (microsoft.public.word.vba.userforms)
  • Re: Populate a Multi-Column ListBox
    ... two-dimensional array to load into a ListBox or ComboBox. ... For a multicolumn ListBox or ComboBox, AddItem inserts an entire row, ... If you populate a ListBox with an Array using the List of Column ... Dim myArray As Variant ...
    (microsoft.public.word.vba.userforms)
  • Re: Populate a Multi-Column ListBox
    ... two-dimensional array to load into a ListBox or ComboBox. ... For a multicolumn ListBox or ComboBox, AddItem inserts an entire row, ... If you populate a ListBox with an Array using the List of Column ... Dim myArray As Variant ...
    (microsoft.public.word.vba.userforms)
  • Re: Populate a Multi-Column ListBox
    ... 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 ... Dim myArray1 As Variant ...
    (microsoft.public.word.vba.userforms)
  • Re: Populate a Multi-Column ListBox
    ... 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 ... Dim myArray1 As Variant ...
    (microsoft.public.word.vba.userforms)