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

Posted on July 6, 2019 by

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

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

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

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

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

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!

Tagged with: add data, Count, edit data, Excel, List, listbox, ListCount, RemoveItem, VBA

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

Adding data

Adding data to existing data

Editing data

Deleting data from a list

Related posts: