VBA – ListBox in Excel – Adding, Editing and Removing Data

The idea of the article is to show how to add, edit and remove specific entries of the ListBox in Excel through VBA:

ListBox is a control, which is available through Developer>Insert>ActiveX>ListBox in Excel:

Once you add it, it has to be controlled through its parent, the worksheet in which it resides.

Adding data

Adding data is quite trivial – the List property works quite ok:

Sub InitiallyFillListBox()

    Dim data() As Variant
    data = Array("vitoshacademy.com", "is the website!")
    Worksheets(1).ListBox1.List = data
    
End Sub

Adding data to existing data

Here we need the all-mighty AddItem . To be honest, it took me about 5 minutes to find how to do it:

Sub AddDataToPresentData()
    
    Dim data As Variant
    With Application
        data = .Transpose(.Transpose(Worksheets(1).Range("E1:E10")))
    End With
    
    Dim entry As Variant
    For Each entry In data
        Worksheets(1).ListBox1.AddItem entry
    Next entry
    
End Sub

Now, the entries from Range(“E1:E10”) are added to the initial entries. (See more for converting Excel range to array here)

Editing data

Editing data is somehow not science finction as well – we simply loop and do the needful. In this case, we would double every entry, starting with “K” or “k”:

Sub DoubleTheKs()
    
    With Worksheets(1)
        Dim i As Long
        For i = 0 To .ListBox1.ListCount - 1
            Dim entry As Variant
            entry = .ListBox1.List(i)
            If UCase(Left(entry, 1)) = "K" Then
                .ListBox1.List(i) = entry & entry & "_Edited"
            End If
        Next i
    End With

End Sub

Here we see the strange property .ListCount, and not List.Count but VBA definitely has a good reason for this:

Deleting data from a list

The easiest way to delete data is to loop through it and to remove the entries we do not like. Looping should be downside-up, in order not to make the index feel uncomfortable:

Sub DeleteNonEdited()
    
    With Worksheets(1)
        Dim i As Long
        For i = .ListBox1.ListCount - 1 To 0 Step -1
            Dim entry As Variant
            entry = .ListBox1.List(i)
            If InStr(1, entry, "_Edited", vbTextCompare) < 1 Then
                .ListBox1.RemoveItem (i)
            End If
        Next i
    End With

End Sub

Thus, KennyKennty_Edited and KrasiKrasi_Edited  are the last to stay:

That’s all folks!