Excel VBA Listbox with additional item number column

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

Code:

---

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

Please use code tags ! and post a sample file.

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

something like this

Code:

---

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

---

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

Maybe renumber as you go through in the first loop

Code:

---

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

---

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?

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.

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

Works perfectly, thank you :-)

What's the purpose of those item numbers ?

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

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