List Boxes

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Can someone tell me how to correct the following code so that both
columns in the listbox will be updated? Only the first column
distplays

Dim MyArray()
ReDim MyArray(mycount, 1)
usrGLDist.lstOutput.ColumnCount = 2

For i = 0 To rst.Count - 1
MyArray(i, 0) = rst.Value("descr")
MyArray(i, 1) = rst.Value("pcamt")
rst.MoveNext
Next i

'Load ListBox1
usrGLDist.lstOutput.List() = MyArray
 
Richard wrote :
Can someone tell me how to correct the following code so that both
columns in the listbox will be updated? Only the first column
distplays

Dim MyArray()
ReDim MyArray(mycount, 1)
usrGLDist.lstOutput.ColumnCount = 2

For i = 0 To rst.Count - 1
MyArray(i, 0) = rst.Value("descr")
MyArray(i, 1) = rst.Value("pcamt")
rst.MoveNext
Next i

'Load ListBox1
usrGLDist.lstOutput.List() = MyArray
Click to expand...

You can't use ReDim on a multi-dim array. Change the code as follows...

Dim MyArray(mycount, 1)

...where (I presume) mycount=rst.Count-1.
 
You can't use ReDim on a multi-dim array.

???? Try this code...

Sub Test()
Dim First As Long, Second As Long
First = 4
Second = 9
ReDim MyArray(First, Second)
MsgBox "Lower Bound: " & UBound(MyArray, 1) & vbLf & _
"Upper Bound: " & UBound(MyArray, 2)
End Sub

Rick Rothstein (MVP - Excel)
 
MsgBox "Lower Bound: " & UBound(MyArray, 1) & vbLf & _
"Upper Bound: " & UBound(MyArray, 2)
Click to expand...

Rather than say "Lower Bound" and "Upper Bound", that should have been
"First Element Upper Bound" and "Second Element Upper Bound". Forget that
though... here is a more complete demonstration.

Sub Test2()
Dim LFirst As Long, UFirst As Long, LSecond As Long, USecond As Long
LFirst = 2
UFirst = 7
LSecond = 4
USecond = 11
ReDim MyArray(LFirst To UFirst, LSecond To USecond)
MsgBox "First Element Lower Bound: " & LBound(MyArray, 1) & vbLf & _
"First Element Upper Bound: " & UBound(MyArray, 1) & vbLf & _
"Second Element Lower Bound: " & LBound(MyArray, 2) & vbLf & _
"Second Element Upper Bound: " & UBound(MyArray, 2) & vbLf
End Sub

Rick Rothstein (MVP - Excel)
 
After serious thinking Rick Rothstein wrote :
Rather than say "Lower Bound" and "Upper Bound", that should have been "First
Element Upper Bound" and "Second Element Upper Bound". Forget that though...
here is a more complete demonstration.

Sub Test2()
Dim LFirst As Long, UFirst As Long, LSecond As Long, USecond As Long
LFirst = 2
UFirst = 7
LSecond = 4
USecond = 11
ReDim MyArray(LFirst To UFirst, LSecond To USecond)
MsgBox "First Element Lower Bound: " & LBound(MyArray, 1) & vbLf & _
"First Element Upper Bound: " & UBound(MyArray, 1) & vbLf & _
"Second Element Lower Bound: " & LBound(MyArray, 2) & vbLf & _
"Second Element Upper Bound: " & UBound(MyArray, 2) & vbLf
End Sub

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks, Rick! So the rule then *still is* that you can't ReDim a
multi-dim array *after it's been dimensioned*, which is not the case in
this OP's context. My bad for not paying attention to the fact that
MyArray() was not dimensioned (thus not declared as a multi-dim array).

Otherwise, the posted code works for me using contrived values.
Perhaps, then, rst.Value("pcamt") is empty, OR is not a valid field.
(Assumes OP is working with a DB recordset)

The OP doesn't claim an error message so I wonder if the code is
covered by an 'On Error Resume Next' statement?

Here's my test...

Private Sub UserForm_Initialize()
Const iCnt As Integer = 3
Dim vListItems() As Variant, i As Integer
ReDim vListItems(iCnt, 1)
For i = 0 To iCnt
vListItems(i, 0) = "Col1: ListItem" & i
vListItems(i, 1) = "Col2: ListItem" & i
Next
With Me.ListBox1
.ColumnCount = 2: .List = vListItems
End With
End Sub

Output to ListBox1:

Col1: ListItem0 Col2: ListItem0
Col1: ListItem1 Col2: ListItem1
Col1: ListItem2 Col2: ListItem2
Col1: ListItem3 Col2: ListItem3
 
Back
Top