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:
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:
And we will create it in the middle of our UserForm:
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”:
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:
|
1 2 3 4 5 6 |
Private Sub UserForm_Initialize() 'Creating and assigning the Array to the List Box Dim mylist As Variant mylist = Array("Argentina", "Brazil", "China", "Europe", "Russia", "USA") ListBox1.list = mylist End Sub |
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:
When we run this code by clicking F5 while in the VBA, this is what we will end up with:
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):
This is the code that we need for this:
|
1 2 3 4 5 6 7 8 9 10 11 |
Private Sub CommandButton1_Click() Dim j As Long 'Loop through the whole list For j = 0 To ListBox1.ListCount - 1 'Verify if anything was chosen If ListBox1.Selected(j) Then 'Insert selected item in the sheet Range("B" & Rows.Count).End(xlUp).Offset(1).Value = ListBox1.list(j) End If Next j End Sub |
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:
