Home Appending new positions to a form listbox

Appending new positions to a form listbox

Andrey
1#
Andrey Published in 2018-02-14 13:44:34Z
 I have a UserForm in Excel with a ListBox, which must show the result of a computation. The problem is when I try to fill the Listbox using a recursive loop the information which was there earlier is replaced with new data. How can I append new information to the content in the ListBox and not lose earlier information? My current code: Dim Form As HistoryFRM, ARR(), i, ArrHistory() ..... Set Form = New HistoryFRM With Form .Show vbModeless .LBHistory.ColumnCount = 6 For i = 0 To UBound(ARR) ArrHistory = SQL_Editor("SELECT * FROM [Table] WHERE [ID]='" & ARR(i) & "';") .LBHistory.Column = ArrHistory Next i End With 
Cindy Meister
2#
Cindy Meister Reply to 2018-02-14 15:22:45Z
 If I understand you correctly, you want to fill six columns with the array you're getting from the data base. Both the List and Countproperties replace the content of their lists when an array is assigned. The AddItem method allows you to append new items to the list, but in one dimension, only. In order to append a new array of items, I believe you first need to read the current list into an array, append the new items to that array, then write the entirety back to the ListBox. Here's an example: Dim arr() Dim lb As ListBox Dim numCols As Long Dim rowCount As Long, colCount As Long Dim numNewRecs As Long, newRecCount As Long Set lb = Me.ListBox1 'You need to know how many new records are coming in 'Substitute this determination here: numNewRecs = 2 numCols = lb.ColumnCount - 1 'Dimension the array for the current list plus the new records ReDim arr(lb.ListCount - 1 + numNewRecs, numCols) 'Get the current list For rowCount = 0 To lb.ListCount - 1 For colCount = 0 To numCols arr(rowCount, colCount) = lb.List(rowCount, colCount) Next Next 'Append the new records For newRecCount = rowCount To rowCount + numNewRecs - 1 For colCount = 0 To numCols arr(newRecCount, colCount) = "New data" & CStr(newRecCount) Next Next 'Populate the ListBox lb.List = arr() 
DisplayName
3#
DisplayName Reply to 2018-02-14 14:39:25Z
 Try this Option Explicit Dim Form As HistoryFRM, ARR() As Variant, ArrHistory() As Variant Dim i As Long, j As Long .... Set Form = New HistoryFRM With Form .Show vbModeless With .LBHistory .ColumnCount = 6 For i = 0 To UBound(ARR) ArrHistory = SQL_Editor("SELECT * FROM [Table] WHERE [ID]='" & ARR(i) & "';") For j = LBound(ArrHistory) To UBound(ArrHistory) .AddItem ArrHistory(j) Next Next End With End With 
 You need to login account before you can post.
Processed in 1.315697 second(s) , Gzip On .