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
When it should be displaying as
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:
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:
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