Introduction of List Box In Excel VBA

Welcome to Excel Avon

Introduction Of List Box

In today’s post we will tell you Introduction of List Box in Excel VBA, In the previous post, we taught you that Data Entry with ComboBox by UserForm in Excel, the list box in Excel VBA is a list assigned to a variable. This list has various inputs to select from and allows selecting multiple options at once. A list box can be inserted on a User Form by choosing the list box option. List Box is one of the tools under User Forms in VBA. As the name suggests, the list box will contain all the list of values entered by the user.

Today we come across an Listbox with a user form. In this article, we will introduce you to listbox and how to create listbox and put values in listbox in Excel VBA.

Introduction Of List Box in VBA

Ok, you should know how to insert module, that’s it, because till now we were working with module, and now we will learn to insert UserForm. Let’s understand. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.

introduction-of-userform

When we click on the User form, a separate wizard box will be created in a window VBE, in which you can see UserForm1 as the default name. You can increase or decrease the size of the UserForm with the mouse pointer.

First of all, we will select the Userform Toolbox label then drag it to the Userform. Label will be named by going to properties of the label.

introduction-of-list-box-userform

Go to toolbox and then select listbox and in Userform we will drag listbox as big as listbox required.

introduction-of-list-box-userform1

We will have some kind of data, by selecting which we will give the name of that range, you can give the name of the range, we have highlighted where you have to name the range, we have given DataRng here.

introduction-of-list-box-userform2

Copy this name and take it to UserForm and then go to the property of UserForm and then go to Row Source and write the name of the range with equal (=Data Range).

introduction-of-list-box-userform (2)

We started seeing the data here, but only one column and we want the complete data, so now we have to go to the properties and make some changes.

introduction-of-list-box-userform 3

We will go to the column count option and then in that we will write the total column as many columns as there will be.

introduction-of-list-box-userform 4

Now you can see that we get the complete data as soon as we update the column count.

introduction-of-list-box-userform 5

Now we will Run Code

introduction-of-list-box-userform 6

After running the code, we will get UserForm like this.

introduction-of-list-box-userform 7

Now go back to VBE and now we will change the distance between header 1 to header 2 so we have to go to UserForm and go to ListBox properties and change the column widths.

introduction-of-list-box-userform 8

When we change and press the enter button, we will see the width of the column changing.

introduction-of-list-box-userform 9

Now we will change the distance between second to third column, for this we have to use semicolon (;).

introduction-of-list-box-userform 10

Here we have changed the distance between second to third column and third to fourth column.

introduction-of-list-box-userform 11

Now we will run the code, after clicking run button we will get result like this, which can see the spacing of the headers.

introduction-of-list-box-userform 12

Now we will go back to VBE again and then there we will activate the multiselect option so that we will be able to select multiple data at once.

introduction-of-list-box-userform 12.png

After activating multi select we will click run button and then listbox will open in worksheet where we can select multiple data values

introduction-of-list-box-userform 14

Come back to VBE again and now change the selection style our listbox will look a bit better.

introduction-of-list-box-userform 15

After changing the selection style, we will see a list box like this.

introduction-of-list-box-userform 16

If now we activate single selection by going to VBE, then list selection style will change, here only one data value will be able to be selected.

introduction-of-list-box-userform 17

Now we will use special effect in ListBox.

introduction-of-list-box-userform 18

Now we will click on the run button, then we will understand the effect of what has changed.

introduction-of-list-box-userform 19

And there was some such UserForm List Box in which you have been introduced to some important functions which you can understand.

Therefore, I hope that you have understand Introduction Of List Box in Excel VBA in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Data Entry with Introduction Of List Box in Excel VBA