Excel VBA Listbox with additional item number column

[KongUK]

Hi

I am trying to populate a listbox that has 2 columns, the first includes the item data and the second just item number i.e.

House 1
Keys 2
Car 3
Trees 4

This is actually listbox2 as I move items from listbox1 across to listbox2 with a command button



Private Sub CommandButton2_Click()
'Selected left to right
Dim iCtr As Long
For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
Me.ListBox2.List(ListBox2.ListCount - 1, 1) = Me.ListBox2.ListCount
End If
Next iCtr
For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox1.RemoveItem iCtr
End If
Next iCtr
End Sub

This works fine when adding but when removing I want the listbox to recalculate the items thus
Keys removed

House 1
Car 2
Trees 3

It seems to work for last or second last item but not for any other item nearer top of list
I have tried looping through items and reassigning the listboxcount and also tried listindex but listindex always gives zero?

I do not want the items resorted in position just the item number

Any help greatly appreciated

thanks

[snb]

Please use code tags ! and post a sample file.

[KongUK]

Ok, sorry about that, I added the code tags. Do I need to create an Excel file with the working code in it, and attach it?

Thanks

[Bob Phillips]

something like this


Private Sub CommandButton3_Click()
'Selected left to right
Dim iCtr As Long
Dim i As Long

With Me.ListBox2

For iCtr = 0 To .ListCount - 1

If Me.ListBox2.Selected(iCtr) = True Then

Me.ListBox1.AddItem .List(iCtr)
End If
Next iCtr

For iCtr = .ListCount - 1 To 0 Step -1

If .Selected(iCtr) = True Then

.RemoveItem iCtr

Exit For
End If

For i = 0 To .ListCount - 1

.List(.ListCount - 1, 1) = i
Next i
Next iCtr
End With
End Sub

[KongUK]

Thanks xld

I tried this but get the same issue I was having. If I have 4 items 1,2,3,4 and remove item 2, I am left with 1,3,3 not 1,2,3 ?
If I have 3 items it works fine 1,2,3, remove 2, leaves 1,2

Thanks

[Aflatoon]

Maybe renumber as you go through in the first loop

Private Sub CommandButton1_Click() 'Selected left to right
Dim iCtr As Long
Dim i As Long

With Me.ListBox2
i = 1
For iCtr = 0 To .ListCount - 1

If .Selected(iCtr) = True Then

Me.ListBox1.AddItem .List(iCtr)
Else
.List(iCtr, 1) = i
i = i + 1
End If
Next iCtr

For iCtr = .ListCount - 1 To 0 Step -1

If .Selected(iCtr) = True Then

.RemoveItem iCtr

End If

Next iCtr
End With
End Sub

[KongUK]

Didn't help I'm afraid :-(

Would it be easier to add items to column 1 first then add incremental numbers to column 2. Then just remove and add on each change to the listbox2 item?

[Aflatoon]

Can you clarify "didn't work". In what way? I just tested that quickly and it works for what I think you're after - i.e. renumbering the items in listbox 2.

[KongUK]

I do apologise, it does work, I had the previous replies suggestion still active in the code

Works perfectly, thank you :-)

[snb]

What's the purpose of those item numbers ?

[KongUK]

They are passed to another sheet to signify priority of the item and also act as a user aid when setting the priority

[snb]

Isn't the order of the items identical to their priority ?