MSForms.ListBox Type Mismatch in Access
- by Jason
I have an Access database where I use a Tab control (without tabs) to simulate a wizard. One of the tab pages has an MSForms.ListBox control called lstPorts, and a button named cmdAdd which adds the contents of a textbox to the List Box. I then try to keep the contents of the ListBox sorted. However, the call to the Sort method causes a type mismatch.
Here is the cmdAdd_Click() code behind:
Private Sub cmdAdd_Click()
Dim test As MSForms.ListBox
lstPorts2.AddItem (txtPortName)
Call SortListBox(lstPorts2)
End Sub
Here is the SortListBox Sub:
Public Sub SortListBox(ByRef oLb As MSForms.ListBox)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant
'Put the items in a variant array
vaItems = oLb.List
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) > vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i
'Clear the listbox
oLb.Clear
'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
oLb.AddItem vaItems(i, 0)
Next i
End Sub
Any help out there? Since the Sort routine explicitly references the MSForms.ListBox, most of the results from Google aren't applicable.
Jason
