What is a ListBox?

ListBox refers to a permanently displayed control (usually box-shaped) which contains a list of objects (or attribute, or elements) from which the user can select single or multiple attributes. The objects or attributes may be text, date, Booleans, pictorial representations, or graphics. The ListBox is usually connected with a text box control where the selected item is loaded or displayed in the list.

VBA Listbox allows the users to create a list of options from which they can choose single or multiple items. In Excel, it is mostly used in Userforms, but Listbox can also be listed in Excel Worksheets. ListBox is one of the UserForm’s control. You can select and drag ListBox on the UserForm. It does not provide any text entry field to type the text value. The default name of its object is ‘ListBox1’, although you can change this name at your convenience.

ListBoxes are helpful and convenient to manage different tables within your user form. It permits users to select their necessary information chosen. With this method, the users can control the rows of information without displaying all the completed data within the userform itself. It restricts the end-user to pick the values only from the given set of data. Thus, it prevents the unnecessary spamming up of data.

ListBox is used where the object and choices are represented textually and are not selected frequently. The listed data is usually not well known to the end-user or cannot quickly be learned or remembered. It is also used if the user wants to align the choices in a non-predictable manner.  For example, in an institute course form, a list box can be used to select which courses the student wants to get enrolled in. He could choose one or multiple based on the given list.
 

Advantages of ListBox

  1. ListBox accepts unlimited number of choices as there is no limit to the list size.
  2. The ListBox is always visible.
  3. With ListBox, multiple selections could be made. The selection for the value can be made easily by pointing and clicking with the mouse.
  4. The ListBox can be easily scrolled to view an extensive list of choices.
  5. It selects the options from a large set of choices that could be mutually or non-mutually exclusive.

Disadvantages of ListBox

  1. ListBox acquires lots of screen space.
  2. The number of displayed elements depends upon the size of the box. Thus, the scrolling action is often required to look after all the choices.
  3. The attributes in the list are often unordered (or ordered unpredictably). Thus, making it tough for the end-user to find the elements.
  4. The content may change, making it tough to search for the items.

Steps to add ListBox to Excel Sheet

Let’s work with the set-by-step code of lines:

  1. Go to your ActiveSheet and click on Developer Tab -> Controls -> Insert
  • The Form and ActiveX Controls window will pop up. From the ActiveX controls window, choose the ListBox control option.
  • Make sure the design mode is enabled. You will notice that the cursor has changed to a ‘+’ symbol. Drag the cursor to shape and position your ListBox. Once done, release the cursor. As shown below, the ListBox will be created.
  • You can rename the ListBox control by right-clicking on the control and selecting the properties option. The properties dialog box appears on the left side of the sheet. The default name for the first list box is ‘ListBox1’. One can change this name at any point. But for our convenience we will keep the default name. Under the caption option sets the control name. You can even change or enable the other properties unlike, ForeColor, BackColor, Height, locked, visible, etc., for this control.
  • Insert a Button control and double click on it. And under the sub procedure you can right the code. Once the code is done disable the design mode. Click on the button. The action for the code will be implemented.

Add Item to ListBox

The ‘AddItem’ method is used to add an item to the Listbox. By default, it ends to items to the end of the lists. But one can also position the value as per their requirement.

  1. Add Item to the End

Syntax

Value – It represents the value (could be int, text, Boolean) you want to add in your list.

Order – It specifies the position where you want to insert your element.   

Example 1

With the help of the above example, add the list box window in your excel sheet. Now, double click on the button control. It will redirect you to the visual editor window where the sub-function is already defined.

Code:

Output

Before running the output just make sure that design mode, is disabled.

  • Add Item to a specific position

Syntax

Value – It represents the value (could be int, text, Boolean) you want to add in your list.

Order – It specifies the position where you want to insert your element.   

Example

Insert the ListBox in your excel sheet. Double click on the list box window and it will redirect you to the VBA editor sheet page with a predefined subprocedure. You just need to write the below code:

Code:

Output

Before running the output just make sure that the mode is disabled.

Add Multiple Items

  1. Individual AddItem

Here, we use multiple ‘AddItem’ method to add items in our ListBox window. This method is very easy to implement. But for more values, it increases the line of codes and is a tedious method. 

Code:

Output

  • Array Listing

Array listing is very useful to add items in the ListBox at one go. It eradicates the code of lines. Thus, making it the code smaller and run time faster.

Code:

Output

  • Adding with Cell Range

This method enables us to fetch the values from the sheet and load the values in run time.

Code:

Output

  • Table Listing

Code:

Output

Delete ListBox

The ‘RemoveItem’ method is used to delete the items from the Excel list (if its present).

Syntax

Value: It represents the position in integer from which you want to delete the value. It starts counting from 0.

  1. Delete one element from the ListBox

Code:

Output

  • To remove only selected element from the list

Code:

Clear Method

This method is used to remove all the elements from the ListBox window.

Code:

Output

Before running the code

After running the code

ListBox Multiple Selection

The MultiSelect property is used to enable the multi selection property in your Excel VBA. You can set it to one of the following values:

  1. fmMultiSelectSingle – This property enables you to select only 1 value. This is the default property.
  2. fmMultiSelectMulti – This property enables the user to select multiple ListBox items. If the user clicks on an item once it will get selected and if he will click on the selected item again, it will get deselected. Thus, will be removed from the existing selected items.
  3. fmMultiSelectExtended – This property enables the user to select multiple ListBox items. In this multiple items can be selected by holding and moving the cursor up and down.

Example 1: Demonstrate an example to enable fmMultiSelectMulti property.

Code:

Output

Example 2: Demonstrate an example to enable fmMultiSelectExtended property.

Code:

Output

Pin It on Pinterest

Share This