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:

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:

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”:

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:

Thus, KennyKennty_Edited and KrasiKrasi_Edited  are the last to stay:

That’s all folks!


A VBA Developer.

Tagged with: , , , , , , , ,