How to Insert Modules in Excel VBA Projects

VBA (Visual Basic for Applications) is a powerful tool in Excel that allows users to automate tasks and enhance functionality. In this guide, we will walk you through the process of inserting a module into your Excel VBA project.

Step 1: Accessing the Developer Tab

Go to the "Developer" tab on the Excel ribbon. If you don't see the Developer tab, you may need to enable it in Excel's options. Check this previous post to learn how to enable the Developer tab in your Excel application.

How to show the Developer tab in Excel

Step 2: Opening the VBA Editor

Click on "Visual Basic" in the Developer tab. This action opens the VBA Editor, where you can create and manage your VBA code.

Step 3: Selecting the Workbook

In the Project Explorer window on the left, click on the workbook for which you want to insert a module. This step is crucial if you have multiple workbooks open.

In this example I’m going to insert the module to the VBA project of the Book2 file.

Step 4: Inserting a Module

From the menu, select "Insert" and then choose "Module." This command adds a new module under the selected workbook in the Project Explorer.

Step 5: Opening the Module

When you insert a new module, that module will be automatically open in the central editing area. If you have multiple modules in one or more VBA projects and if you want to add/edit or view code in a particular module, then double-click on the module you want. This action opens that particular module in the central editing area, allowing you to add/edit or view your VBA code.

Inserting a module provides a foundation for incorporating custom automation and functionalities into your Excel projects. With this guide, you can now easily insert VBA modules into your workbooks.

Populate Userform Listbox from Range

In this lesson you will learn how to populate a userform listbox with a range. In VBA (Visual Basic for Applications), a list box is a graphical user interface control that allows users to make a selection from a list of items. It is commonly used in user forms to display data or present a list of options from which the user can choose. When it is used to present a list from which the user can select, the selected item or items in the list box can then be used for further processing in the other VBA codes associated with controls such as command buttons. Or by codes even associated with events such as listbox_click events etc.

A list box can contain a collection of items, typically displayed in a vertical list. These items can be static (defined at design time) or dynamic (populated at runtime). In this example we will use a collection of static items stored in a worksheet. If you want to populate the list box with a dynamic data set, then the best option is to populate data from an array. This post explains how to populate a listbox from an array.

Fill a Listbox From an Array

Name of this worksheet is “EmployeeData”. This worksheet contains Full Name, Job Title, Department, Employee ID and Date of Joining for 10 employees. Now let’s see how we can populate these data into a userform listbox. This is the listbox we are going to use for this example. Name of the form is frmEmployeeData and name of the listbox is lstEmployeeData.

Now we can write the vba code inside the UserForm_Initialize event to populate the listbox with Excel range.

Private Sub UserForm_Initialize()

End Sub

This is how we can populate the list with the above Excel range.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=EmployeeData!A1:E11"
     End With

End Sub

This is how the listbox is populated from the Excel data range.

Remember that you can’t use worksheet names having spaces.In the above example we use an Excel sheet called “EmployeeData”. Assume our worksheet name is “Employee Data”. Then we can’t modify the code as follows.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=Employee Data!A1:E11"
     End With

End Sub

Because then it will throw an error like this.