Home Appending new positions to a form listbox
Reply: 2

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 1.315697 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO