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 ?