What am I doing wrong

[BoredBloke]

Hi folks,

I have a listbox in a form in excel which I have populated with data. I want to get this into alphabetical order. I have been trying to pass the contents of the listbox to a routine which carries out a bubble sort. Unfortunately it complains with an error saying 'object required'

my listbox is called lstAll

the routine it is going to is called

sortListBox(olb as MSForms.listbox)

What sytax should I use to get it to work? Is there something blindingly obvious I'm missing?

[BoredBloke]

code in full

Sub PopulateAll()

Dim lgLastRow As Long
Dim lgRowCounter As Long
Dim intCounter As Integer
Dim olb As MSForms.ListBox

For intCounter = 0 To UBound(vAllEnv)
Me.lstAll.AddItem (vAllEnv(intCounter))

Next intCounter

Set olb = frmOptions.lstAll

SortListBox (olb)

End Sub


Sub SortListBox(ByRef olb As MSForms.ListBox)
Dim vItems As Variant
Dim i As Long
Dim j As Long
Dim vTemp As Variant

vtiems = olb.List

For i = LBound(vItems, 1) To UBound(vItems, 1) - 1
For j = i + 1 To UBound(vItems, 1)
If vItems(i, 0) > vItems(j, 0) Then
vTemp = vItems(i, 0)
vItems(i, 0) = vItems(j, 0)
vItems(j, 0) = vTemp
End If
Next j
Next i

olb.Clear

For i = LBound(vItems, 1) To UBound(vItems, 1)
olb.AddItem vItems(i, 0)
Next i
End Sub

[MrRobin]

Why don't you just pass your array, vAllEnv, through the bubble sort first before it populates your list box, rather than trying to send the object itself.

[BoredBloke]

I think that is what I'm going to do - I was told that this should work but couldn't find out why it didn't

Cheers

[MrRobin]

Also just noticed you have spelt your variable wrong...

vtiems = olb.List

Use option explicit!

[BoredBloke]

Spotted that - cheers.

Got it working. Took the brackets off from the line SortListBox (olb)