Get Value From Listbox Using VBA

With Excel and VBA especially, options for everyone are virtually limitless. In VBA, users can create Macros, UserForms, and ClassModules.

One of the most used and convenient options with UserForms is ListBoxes, which is an integral part of it.

In the example below, we will show how can get value from ListBox with VBA.

Creating ListBox

First things first, our mission is to create the UserForm. To do so, we open up the VBA by clicking ALT + F11 on our keyboard. In the Insert menu, or by right-clicking in the left window, we will choose UserForm:

Graphical user interface, application

Description automatically generated

We need to add a Toolbox to manipulate with UserForm. To do so, we will click on the View tab, and then choose Toolbox:

In the Toolbox, we will choose the ListBox option:

Graphical user interface, application, Teams

Description automatically generated

And we will create it in the middle of our UserForm:

Graphical user interface

Description automatically generated with medium confidence

We will now add the CommandButton from the Toolbox as well, to control the UserForm. We will then change the caption of UserForm to “Country”, and the caption of CommandButton to “Select A Country”:

A picture containing diagram

Description automatically generated

Create a List in Listbox

Now we need to add some code to our UserForm. At this moment, it is very important to remember that the ListBox is a part of UserForm, so we need to change the code in UserForm. We will double-click on it, and then insert the following code:

This code creates a variable mylist as a variant and then defines it as an array containing several countries. Then it defines that this variable will be equal to our ListBox, meaning that these countries will appear when we run the code. This is what the code looks like in the VBA:

Graphical user interface, text

Description automatically generated

When we run this code by clicking F5 while in the VBA, this is what we will end up with:

Graphical user interface, table

Description automatically generated

Sadly, this code does not do anything with the UserForm, but rather just shows the list.

Get Value From Listbox

We need additional code to get the value into the workbook. For this, we will first create the table with students and the country they are from (this field is to be populated):

Graphical user interface, application, table

Description automatically generated

This is the code that we need for this:

This code is run when the command button is clicked. First, we define the “j” variable as long, then we use For Next Loop where we will define that the selected country ends up in column B, but only if a certain country from the list is selected.

When we execute the code by pressing F5 on the keyboard while in the VBA, we will get the same screen as before, but now, when we select any country on the list, it will be inserted in our table:

Graphical user interface, text, application

Description automatically generated

Posted in vba