EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Message Functions VBA List Box
 

VBA List Box

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 12, 2023

VBA List Box

 

 

What is List Box in Excel VBA?

List Box is one of the tools under user form in VBA. As the name itself suggests, list box will hold all the list of values given by the user. In VBA List box comes with a user form. In this article, we will see how to create a list box and hold values in the list box in Excel VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

A list Box is a box that can hold values in it. The user can select the item shown in the list box from this list of items.

We can also draw a list box in a regular Excel sheet.

How to Create List Box in Excel?

Assume you have a list of the month in one of the Excel sheets.

VBA List Box Example 1-1

For this list of months, we will create a list box. Follow the below steps to create the List Box in the Excel worksheet:

Step 1: Go to Developer Tab. Under the developer tab, Go to the Insert option; under the insert option, Go to Form Control; under form control, select List Box.

VBA List Box Example 1-2

Step 2: After selecting the list box, draw the list box in the Excel sheet.

VBA List Box Example 1-3

Step 3: Right-click on the inserted list box and select Format Control.

VBA List Box Example 1-4

Step 4: Under Format Control, go to Control. Select the input range as the month name range, give the cell reference to cell D1, and then click Ok.

VBA List Box Example 1-5

Step 5: We will have a list of the month in the list box.

VBA List Box Example 1-6

Step 6: Select any items in the list box and see the change in cell D1.

VBA List Box Example 1-7

So, we got 3 in cell D1 because Mar is the third item on the list.

How to Create List Box in VBA?

Create a list box is not that easy; as we have seen in the above example, you need to have a considerable amount of knowledge of Excel VBA Macros.

You can download this VBA List Box Excel Template here – VBA List Box Excel Template

VBA List Box – Example #1

Understanding of VBA List Box.

You can consider the following example to understand the VBA List Box better.

Follow the below steps to create a list box in VBA.

Step 1: In the Visual Basic window, Insert the User form.

VBA List Box Example 2-1

Step 2: As soon as you insert the user form, you can see the toolbox along with the user form.

VBA List Box Example 2-2

Step 3: In this toolbox, select List Box.

VBA List Box Example 2-3

Step 4: Draw this list box on the user form.

VBA List Box Example 2-4

Step 5: After inserting the list box, give a proper name to this list box under the Properties window.

VBA List Box Example 2-5

Note: If you don’t see this list box, press the F4 key, and you will see a properties window like the above one.

Step 6: Scroll down and find “Row Source.” In this, type the range of values of your month name along with the sheet name.

VBA List Box Example 2-6

Step 7: As soon as you give the reference, you can see the month names in the list box.

VBA List Box Example 2-7

Step 8: Now, in the list box properties window, select line style as “1 – fmListStyleOption“.

VBA List Box Example 2-8

Step 9: You can see the impact immediately when you select the line style.

Example 2-9

Step 10: Run this code; you will see a list box separately.

Example 2-10

Step 11: Now, we will add a macro code to store the data selected by the user. Double-click on the list box. You will see a separate macro name like the one below.

Private Sub Month_List_Box_Click()

End Sub

VBA List Box Example 2-11

Step 11: When the user selects the month, we store the data in cell G5. To store the input given by the user, add the below code.

Private Sub Month_List_Box_Click()

  Range("G5").Value = Month_List_Box.Value

End Sub

VBA List Box Example 2-12

Step 12: If you run the code and select any of the months, we can see the value the user selects in the G5 cell.

VBA List Box Example 2-13

I have selected Jun so that I can see Jun in the G5 cell. Like this, we can use a list box in your VBA projects to take input from the users.

VBA List Box – Example #2

Add.Items Method to Add Values to List Box

Follow the below steps to create a list box in VBA.

Step 1: Create a New UserForm. Here it is, UserForm2.

Step 2: Add List box in Userform 2.

Example 3-1

Step 3: After inserting the list box, give a proper name to this list box under the Properties window, i.e., Month_list_Box1.

Example 3-2

Step 4: Now, in the list box properties window, select line style as “1 – fmListStyleOption“.

Example 3-3

Step 5: Double-click on the user form. You will see an auto-inserted macro like the below one.

Private Sub UserForm_Click()

End Sub

Example 3-4

Step 6: By placing a cursor inside the macro, select “Initialize.”

Example 3-5

Step 7: As soon as you selected “Initialize”, we can see a new macro to be created just below the current one.

Private Sub UserForm_Initialize()

End Sub

Example 3-6

Step 8: Delete the old macro. Under UserForm, write the code below to add items to the list box.

Private Sub UserForm_Initialize()

Month_List_Box1.AddItem "Jan"
Month_List_Box1.AddItem "Feb"
Month_List_Box1.AddItem "Mar"
Month_List_Box1.AddItem "Apr"
Month_List_Box1.AddItem "May"
Month_List_Box1.AddItem "Jun"
Month_List_Box1.AddItem "Jul"
Month_List_Box1.AddItem "Aug"
Month_List_Box1.AddItem "Sep"
Month_List_Box1.AddItem "oct"
Month_List_Box1.AddItem "Nov"
Month_List_Box1.AddItem "Dec"

End Sub

VBA List Box Example 3-4

Step 10: Now press the F5 button to run the code, and you will get the following output.

VBA List Box Example 3-5

Things to Remember

  • You need to specify the sheet name and cell reference if you give cell references directly. (You need to add an exclamation (!) symbol in front of the sheet name).
  • You can also add items to the list box by using Add.Item method.
  • If you want to allow the user to select more than one item, you need to select the “fmMultiSelectMult” option under Multi-Select in the Properties window.

VBA List Box Multi-Select

Recommended Articles

This is a guide to VBA List Box. Here we discuss how to Create a List Box in Excel and VBA, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VBA Replace
  2. VBA ByVal
  3. Excel VBA Macro
  4. VBA XML

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA List Box Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - ENROLL NOW