Tony,
There is a lot of code involved here and while I still don't understand why,
it appears that the cause of the unexpectedly high UBound value is due to
the method I used to construct the ListBox. I have found an alternate
method which works and returns the expected UBound value. I will try to
illustrate and explain both and maybe you can see the issue and determine
the cause.
I have a document that contains a 5 row x 3 column table of data. I have a
userform that has three multi-column (3) listboxes and three command
buttons. I use the following code to initialize the userform:
Private Sub UserForm_Initialize()
Dim myArray() As Variant
Dim oDoc 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 oDoc = ThisDocument
i = oDoc.Tables(1).Rows.Count
j = oDoc.Tables(1).Columns.Count
ListBox1.ColumnCount = j
'Hide las two columns
ListBox1.ColumnWidths = "40;0;0"
ReDim myArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = oDoc.Tables(1).Cell(m + 1, n + 1).Range
myitem.End = myitem.End - 1
myArray(m, n) = myitem.Text
Next m
Next n
ListBox1.List() = myArray
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox2.ColumnCount = j
ListBox2.ColumnWidths = "40;40;40"
ListBox3.ColumnCount = j
ListBox3.ColumnWidths = "40;40;40"
End Sub
As you can see, the intialize event simply populates ListBox1 with the
values in the table. Listbox1 is a multi-select listbox. After a user
selects one or more entries in ListBox1 they press the commandButton1 which
is used to populate ListBoxes2 and 3 with the selections made in ListBox1.
The following code is used to populate ListBox2 and ListBox3. In this code
I used the two different methods that I mentioned above.
Method 1 (the one that ultimately works without the unexpected UBound
return)iuses an array and the ListBox.List method. I first cycle through
the list members of ListBox1 to see how many items are selected. I ReDim
the array. I cycle through the list members of ListBox1 again and add the
selected data to the array. And finally, build ListBox2 using the .List
method.
Method 2 (the one that later presents the unexpected UBound return) uses the
..AddItem and .List methods to build ListBox3
Private Sub CommandButton1_Click()
Dim i As Long
Dim x As Long
Dim y As Long
Dim arrTest() As Variant
Me.ListBox2.Clear
Me.ListBox3.Clear
'Populate Listbox2. Method 1
x = -1
'Determine number of elements needed in array
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
x = x + 1
End If
Next i
'Size the array
ReDim arrTest(x, Me.ListBox1.ColumnCount - 1)
'Build the array
x = 0
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
For y = 0 To Me.ListBox1.ColumnCount - 1
arrTest(x, y) = ListBox1.List(i, y)
Next y
x = x + 1
End If
Next i
'Populate the listbox
Me.ListBox2.List = arrTest()
'************************************************
'Poplulate ListBox3. Method 2
'Cylcle through each item in the listindex
x = 0
For i = 0 To Me.ListBox1.ListCount - 1
'If the item is selected add it to ListBox2
If Me.ListBox1.Selected(i) Then
With ListBox3
.AddItem
.List(x, 0) = ListBox1.List(i, 0)
.List(x, 1) = ListBox1.List(i, 1)
.List(x, 2) = ListBox1.List(i, 2)
End With
x = x + 1
End If
Next i
'Cycle through each item in the list and clear the selection.
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
Next i
End Sub
After ListBoxs 2 and 3 are populated. commandButton2 is used to build two
tables in the document populated with the data shown in ListBoxes 2 and 3.
Here is the code for doing that. The comments with the expectations are
based on a user selecting 3 items from ListBox1:
Private Sub CommandButton2_Click()
Dim myArray() As Variant
Dim oRng As Word.Range
Dim oTbl As Word.Table
Dim i As Long
Dim j As Long
'Build table 1
Set oRng = ActiveDocument.Bookmarks("oTbl2").Range
oRng.Select
MsgBox Me.ListBox2.ColumnCount 'Returns 3 as expected
MsgBox Me.ListBox2.ListCount 'Returns 3 as expected
myArray() = Me.ListBox2.List
MsgBox UBound(myArray, 1) 'Returns 2 as expected
MsgBox UBound(myArray, 2) 'Reuturns 2 as expected
For i = 0 To UBound(myArray, 1)
For j = 0 To UBound(myArray, 2)
On Error GoTo Err_Handler
'MsgBox myArray(i, j) 'As a result, this line will throw and error if j
ReEntry:
Next j
Next i
Set oTbl = ActiveDocument.Tables.Add(Selection.Range, _
Me.ListBox2.ListCount, Me.ListBox2.ColumnCount)
For i = 0 To oTbl.Columns.Count - 1
For j = 0 To oTbl.Rows.Count - 1
oTbl.Cell(j + 1, i + 1).Range.Text = myArray(j, i)
Next j
Next i
Erase myArray()
'Build table 2
Set oRng = ActiveDocument.Bookmarks("oTbl3").Range
oRng.Select
MsgBox Me.ListBox3.ColumnCount 'Returns 3 as expected
MsgBox Me.ListBox3.ListCount 'Returns 3 as expected
myArray() = Me.ListBox3.List
MsgBox UBound(myArray, 1) 'Returns 2 as expected
MsgBox UBound(myArray, 2) 'Reuturns 9!! Why? I expect 2.
For i = 0 To UBound(myArray, 1)
For j = 0 To UBound(myArray, 2)
On Error GoTo Err_Handler2
'MsgBox myArray(i, j) 'As a result, this line will throw and error if j
ReEntry2:
Next j
Next i
Set oTbl = ActiveDocument.Tables.Add(Selection.Range, _
Me.ListBox3.ListCount, Me.ListBox3.ColumnCount)
For i = 0 To oTbl.Columns.Count - 1
For j = 0 To oTbl.Rows.Count - 1
oTbl.Cell(j + 1, i + 1).Range.Text = myArray(j, i)
Next j
Next i
Me.Hide
Exit Sub
Err_Handler:
'MsgBox "Error processing j. j = " & j
Resume ReEntry
Err_Handler2:
'MsgBox "Error processing j. j = " & j
Resume ReEntry2
End Sub
The question remains why does Msgbox UBound(myArray, 2) return 9 when method
2 is used to construct ListBox3?
Thanks.
