• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

What am I doing wrong

BoredBloke

Godlike
Joined
Jul 6, 2005
Messages
7,235
Likes received
209
Thanks received
41
Location
whereever the work is
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?
 
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
 
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.
 
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
 
Also just noticed you have spelt your variable wrong...

vtiems = olb.List

Use option explicit! :)
 
Spotted that - cheers.

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

Advertisers

Back
Top