How to delete a line in a listbox and refresh the listcount?

[Kimberley]

Hi,

I'm trying to configure a button to delete entries in a listbox. The listbox has 3 columns : 1/ entries number 2/ male name 3/ female name. Columns 2 and 3 always come together, so when one moves, the other is moving too. Same thing with deletion, but it shouldn't delete the entry number as it will be reaffected to the next row.

Example:

01   Todd	Britney
02   Jimi	Meredith
03   Serge	Amy
04   Richard	Kim
05   Roger	Gabrielle
06   Mike	Tammy
07   Bill	Jill
08   Jim	Joyce
09   Phil	Judy
10   Bebel	Jules
11   Fredrik	Helen
12   James	Yolanda

If I delete couple #2, Serge + Amy would now be couple #2, Richard + Kim would become couple #3 [...] James + Yolanda would become couple #11 and the #12 would be deleted:

01   Todd	Britney
02   Serge	Amy
03   Richard	Kim
04   Roger	Gabrielle
05   Mike	Tammy
06   Bill	Jill
07   Jim	Joyce
08   Phil	Judy
09   Bebel	Jules
10   Fredrik	Helen
11   James	Yolanda

The xlsm can be downloaded there:

UserForm.xlsm - 0.03MB

Thanks in advance!

 

[vaskov17]

replace the "Effacer_click" code with the following:

(new code is red)

Private Sub Effacer_Click()
    [COLOR="Red"]Dim i As Integer[/COLOR]
    
    With ListBox1
    
    If Not .ListIndex = 0 And Not .Value = vbNullString Then
        .RemoveItem (.ListIndex)
        
        [COLOR="red"]For i = .ListIndex To .ListCount - 1
            .List(i, 0) = .List(i, 0) - 1
        Next i[/COLOR]

    End If
    End With
End Sub

 

[Kimberley]

Hi, thanks it's working for entries from 2 to 11. I can't delete the 1st entry and when I delete the last one, the number column is messed up.

 

[vaskov17]

try this (changes from original are highlighted):

Private Sub Effacer_Click()
    [COLOR="Red"]Dim i As Integer[/COLOR]
    
    With ListBox1
    
    If Not .ListIndex = [COLOR="red"]-1[/COLOR] And Not .Value = vbNullString Then
        .RemoveItem (.ListIndex)
        
        [COLOR="red"]If .ListIndex <> .ListCount - 1 Then
            For i = .ListIndex To .ListCount - 1
                .List(i, 0) = .List(i, 0) - 1
            Next i
        End If[/COLOR]
    End If
    End With
End Sub

 

[Kimberley]

Working perfectly, thanks!

 

[vaskov17]

Just remember that for the list box the first item is indexed -1 not 0 and the last item is indexed ListCount-1