Add a single record to a multiple-column listbox in a loop from array

TomPC

Board Regular
Joined
Oct 13, 2011
Messages
95
Hi All

Win7/2010

I have an array PeopleList(6,320) that contains

PersonID, FirstName, LastName, Email, Phone, Notes


What I'm trying to do

I have two separate requirements:

(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()

I have two errors:


Error 1 in UserForm_Initialize()

The listbox contents need transposing! It is displaying as

Code:
[COLOR=#333333]1           2           3           4           5[/COLOR]
Tom         Ben         Heidi       Julie       Mark
Smith       Jones       Evans       Simpson     Petersen
x@yo.com    a@bo.com    c@do.com    e@fo.com    g@ho.com
[COLOR=#333333]02071001022 02071001026 02071001027 02071001028 02071001029
[/COLOR]Friend

When it should be displaying as

Code:
[COLOR=#333333]Code:[/COLOR]
1   Tom Smith   x@yo.com    02071001022 Friend[COLOR=#333333]   
2   Ben Jones   a@bo.com    02071001026    [/COLOR]

Is there a way to transpose the array? (Alternatively, solving my error 2 would be helpful enough)


Error 2 in txtSearchTerm_Change()
I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:

Code:
    For i = 0 To UBound(SearchList)        If InStr(1, SearchList(i), SearchTerm) <> 0 Then
            With lstPeople
                .AddItem
                For j = 0 To UBound(PeopleList, 1)
[COLOR=#ff0000]                    .List(c, j).Value = PeopleList(j, i)[/COLOR]
                Next j
            End With
            c = c + 1
        End If
    Next i

but .List(c, j).Value = PeopleList(j, i) is throwing a Runtime error 424 "Object Required"

How do I add a record to the listbox????


Full code for reference:

Code:
Option Explicit

Private PeopleList As Variant
Private SearchList As Variant


Private Sub UserForm_Initialize()
    
    Dim SQL As String
    Dim i As Long
    Dim j As Long
    
    'Get People list from tblPeople
    SQL = "SELECT PersonID, FirstName, LastName, Email, ContactNumber, Notes "
    SQL = SQL & "FROM tblPeople "
    SQL = SQL & "WHERE Category <> '[System]'"
    
    PeopleList = GetData(SQL)
    ReDim SearchList(UBound(PeopleList, 2))
    For i = 0 To UBound(PeopleList, 2)
        SearchList(i) = ""
        For j = 1 To UBound(PeopleList, 1)
            SearchList(i) = SearchList(i) & Replace(Trim(IIf(IsNull(PeopleList(j, i)), "", PeopleList(j, i))), " ", "")
        Next j
    Next i
       
    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.List = PeopleList
        .Show
    End With
    
End Sub


Private Sub txtSearchTerm_Change()


    Dim i As Long
    Dim j As Long
    Dim SearchTerm As String
    Dim c As Long
    
    lstPeople.Clear
    c = 0
    
    SearchTerm = Replace(Trim(txtSearchTerm.Text), " ", "")
    
    For i = 0 To UBound(SearchList)
        If InStr(1, SearchList(i), SearchTerm) <> 0 Then
            With lstPeople
                .AddItem
                For j = 0 To UBound(PeopleList, 1)
                    .List(c, j).Value = PeopleList(j, i)
                Next j
            End With
            c = c + 1
        End If
    Next i


End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
to solve #1 it's simply:

Application.Transpose(MyArray)

As far as issue #2, you can certainly add rows, but unfortunately I haven't done it in a long time and I don't have my old files in front of me right now to review. But don't give up!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
When you add using list don't use Value.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,759
Some pointers:

1. If you've got 6,000 records, search in the database, I'd guess it would be faster, so don't init with all the records - make the user type into the search box and return only the relevant records.
2. Don't ever, ever, ever use .AddItem for more than about 5 items, it's extremely slow. You can dump your array into the listbox simply with:
Code:
    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.Column = PeopleList
        PeopleList = .lstPeople.List 'Transpose our data
        .Show
    End With

Note the .Column this will automagically transpose the data.

I think the total code should look something like this (untested):
Code:
Option Explicit


Private PeopleList As Variant
Private SearchList As Variant


Private Sub UserForm_Initialize()
    
    Dim SQL As String
    Dim i As Long
    Dim j As Long
    
    'Get People list from tblPeople
    SQL = "SELECT PersonID, FirstName, LastName, Email, ContactNumber, Notes "
    SQL = SQL & "FROM tblPeople "
    SQL = SQL & "WHERE Category <> '[System]'"
    
    PeopleList = GetData(SQL)
    
    ReDim SearchList(UBound(PeopleList, 2))
    For i = 0 To UBound(PeopleList, 2)
        SearchList(i) = i & "~~"
        For j = 1 To UBound(PeopleList, 1)
            SearchList(i) = SearchList(i) & LCase(Replace(Trim(IIf(IsNull(PeopleList(j, i)), "", PeopleList(j, i))), " ", ""))
        Next j
    Next i
       
    With frmSearchPeople
        .lstPeople.ColumnCount = UBound(PeopleList, 1) + 1
        .lstPeople.Column = PeopleList
        PeopleList = .lstPeople.List 'Transpose our data
        .Show
    End With
    
    
    
End Sub




Private Sub txtSearchTerm_Change()




    Dim i As Long
    Dim j As Long
    Dim SearchTerm As String
    Dim c As Long
    Dim filteredArray
    Dim NewData() As Variant


    
    SearchTerm = Replace(Trim(txtSearchTerm.Text), " ", "")
    filteredArray = Filter(SearchList, SearchTerm)
    
    ReDim NewData(LBound(PeopleList) To UBound(filteredArray), LBound(PeopleList, 2) To UBound(PeopleList, 2))
    
    For i = LBound(filteredlist) To UBound(filteredlist)
        c = Val(filteredArray(i))
        For j = LBound(PeopleList, 2) To UBound(PeopleList, 2)
            NewData(i, j) = PeopleList(c, j)
        Next j
    Next i


    lstPeople.List = NewData




End Sub
 

TomPC

Board Regular
Joined
Oct 13, 2011
Messages
95
Thank you all for your replies: in particular, Kyle. I'll try it and let you know how I got on! Thanks, Tom.
 

Forum statistics

Threads
1,185,966
Messages
5,955,053
Members
438,181
Latest member
viv1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top