How To Fill a ListBox control with values from closed workbook using VBA?
Tags: tofillalistboxcontro, vbamacros
Published: 2021-10-22 09:28:16

---
title: How To Fill a ListBox control with values from closed workbook using VBA?
published: true
date: 2021-10-21 09:51:02 UTC
tags: ToFillAListBoxContro,VBAMacros
canonical_url: https://geekexcel.com/how-to-fill-a-listbox-control-with-values-from-closed-workbook-using-vba/
---

In this post, we will guide you on how to  **fill a list box control with values from a closed workbook** using VBA in Microsoft Excel. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Example

- Firstly, you need to **create a sample data** on the **range A2:B10**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Sample-data-36.png)<figcaption>Sample data</figcaption>

- Now, you have to **create two command buttons** on the main worksheet for running two different user forms, and **each command button** is linked to **different user forms**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Create-two-command-buttons.png)<figcaption>Create two command buttons</figcaption>

- In the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
Option Explicit

Sub running()

UserForm1.Show

End Sub

Sub ADODBrunning()

UFADODB.Show

End Sub

'Add below code in UFADODB userform
Option Explicit

Private Sub CommandButton1_Click()
      
Dim name1 As String
Dim age1 As Integer
Dim i As Integer

'Assign the selected value in list box to variable name1 and age1
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        name1 = ListBox1.Value
        age1 = ListBox1.List(ListBox1.ListIndex, 1)
        Exit For
    End If
Next

'Unload the userform
Unload Me

'Displaying output
MsgBox "You have selected " & name1 & ". His age is " & age1 & " yrs."

End Sub

Private Sub UserForm_Initialize()

'Filling ListBox1 with data from a closed workbook

Dim tArray As Variant

'Calling function ReadDataFromWorkbook for getting data from specified range to array
'Change path according to your requirement, "Sample_data" is named defined range
tArray = ReadDataFromWorkbook("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data")

'Calling function FillListBox for adding items in List Box
'Assign List box object and tarray as parameter
FillListBox Me.ListBox1, tArray

'Releasing array variables and deallocate the memory used for their elements.
Erase tArray

End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)

'Filling List box lb with data from RecordSetArray

Dim r As Long, c As Long

With lb
    .Clear
    
    'Assigning value to listbox
    For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
        .AddItem
        For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
            .List(r, c) = RecordSetArray(c, r)
        Next c
    Next r
    
    'Selecting no item in the List box by default
    .ListIndex = -1
End With
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, _
    SourceRange As String) As Variant

' requires a reference to the Microsoft ActiveX Data Objects library
' (menu Tools > References in the VBE)

Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String

'Declaring a connection string and the driver require for establishing connection
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile

'Creating a new ADODB connection
Set dbConnection = New ADODB.Connection

On Error GoTo InvalidInput

'Open the database connection
dbConnection.Open dbConnectionString

'Getting the recordset from defined named range
Set rs = dbConnection.Execute("[" & SourceRange & "]")

On Error GoTo 0

'Returns a two dimensional array with all records in rs
ReadDataFromWorkbook = rs.GetRows

'Close the recordset and database connection
rs.Close
dbConnection.Close

Set rs = Nothing
Set dbConnection = Nothing

Exit Function

'Code for handling error
InvalidInput:
MsgBox "The source file or source range is invalid!", _
    vbExclamation, "Get data from closed workbook"

End Function

'Add below code in UserForm1

Option Explicit

Private Sub CommandButton1_Click()
      
Dim name1 As String
Dim i As Integer

'Assign the selected value to variable name1
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        name1 = ListBox1.Value
        Exit For
    End If
Next

'Unload the userform
Unload Me

'Display the selected name
MsgBox "You have selected " & name1 & "."

End Sub

Private Sub UserForm_Initialize()

Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook

'Turning screen updates off
Application.ScreenUpdating = False

With Me.ListBox1
    'Remove existing entries from the listbox
    .Clear

    'Open the source workbook as ReadOnly
    Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _
        False, True)
    
    'Get the range of values you want
    ListItems = SourceWB.Worksheets(1).Range("A2:A10").Value
    
    'Close the source workbook without saving changes
    SourceWB.Close False

    Set SourceWB = Nothing

    Application.ScreenUpdating = True
        
    'Convert values to a vertical array
    ListItems = Application.WorksheetFunction.Transpose(ListItems)
    
    For i = 1 To UBound(ListItems)
        'Populate the listbox
        .AddItem ListItems(i)
    Next i
    
    'Selecting no items by default, set to 0 to select the first item
    .ListIndex = -1
End With

End Sub

```

- After that, you need to  **save the code**  by selecting it and then  **close the window**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Save-the-Code-69-993x1024.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- Then, you need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Run-the-Code-57.png)<figcaption>Run the Code</figcaption>

- Finally, after running the macro, you will get output as **list box control with values** from a closed workbook in Excel.

## Bottom-Line

We hope that this post gives you guidelines on how to  **fill a list box control with values from a closed workbook** using VBA in Microsoft Excel. Please leave a comment in case of any  **queries,**  and don’t forget to mention your valuable  **suggestions**  as well. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **Read more on [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Further Reference:**

- **[Simple Shortcut to Move to the Next Control in MS Excel 365!!](https://geekexcel.com/simple-shortcut-to-move-to-the-next-control-in-ms-excel-365/)**
- **[Easy Shortcut to Move to the Previous Control in Excel 365!!](https://geekexcel.com/easy-shortcut-to-move-to-the-previous-control-in-excel-365/)**
- **[How To Control Word from Excel using VBA in Microsoft Excel 2010?](https://geekexcel.com/how-to-control-word-from-excel-using-vba-in-microsoft-excel-2010/)**
- **[How To Control Excel from Word using VBA in Microsoft Excel 2010?](https://geekexcel.com/how-to-control-excel-from-word-using-vba-in-microsoft-excel-2010/)**
- **[Use A Closed Workbook As A Database (ADO) Using VBA In Excel](https://geekexcel.com/how-to-use-a-closed-workbook-as-a-database-ado-using-vba-in-excel/)**