How To Fill a List Box with unique values from a worksheet using VBA in Excel?
Tags: tofillalistboxwithun, vbamacros
Published: 2021-10-14 02:41:50

---
title: How To Fill a List Box with unique values from a worksheet using VBA in Excel?
published: true
date: 2021-10-12 11:13:18 UTC
tags: ToFillAListBoxWithUn,VBAMacros
canonical_url: https://geekexcel.com/how-to-fill-a-list-box-with-unique-values-from-a-worksheet-using-vba-in-excel/
---

In this post, we will guide you on how to  **fill a list box with unique values from a Worksheet 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** , and it will **insert in the List Box, consists of names**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Sample-data-24.png)<figcaption>Sample data</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

'Add below code in userform
Option Explicit
Private Sub CommandButton1_Click()   
Dim var1 As String
Dim i As Integer
'Looping through all the values present in the list box
'Assigning the selected value to variable var1
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
        var1 = ListBox1.List(i)
        Exit For
    End If
Next
'Unload the userform.
Unload Me
'Displaying the selected value
MsgBox "You have selected following name in the List Box : " & var1
End Sub

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
'Calling UniqueItemList function
'Assigning the range as input parameter
MyUniqueList = UniqueItemList(Range("A12:A100"), True)
    With Me.ListBox1
        'Clearing the List Box content
        .Clear
        
        'Adding values in the List Box
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        
        'Selecting the first item
        .ListIndex = 0
        
    End With
End Sub

Private Function UniqueItemList(InputRange As Range, _
    HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long
'Declaring a dynamic array
Dim uList() As Variant
'Declaring this function as volatile
'Means function will be recalculated whenever calculation occurs in any cell
Application.Volatile
On Error Resume Next
'Adding items to collection
'Only unique item will be inserted
'Inserting duplicate item will through an error
For Each cl In InputRange
    If cl.Value <> "" Then
        'Adding values in collection
        cUnique.Add cl.Value, CStr(cl.Value)
    End If
Next cl
'Initializing value return by the function
UniqueItemList = ""
If cUnique.Count > 0 Then
    'Resizing the array size
    ReDim uList(1 To cUnique.Count)
    'Inserting values from collection to array
    For i = 1 To cUnique.Count
        uList(i) = cUnique(i)
    Next i
    
    UniqueItemList = uList
    
    'Checking the value of HorizontalList
    'If value is true then transposing value of UniqueItemList
    If Not HorizontalList Then
        UniqueItemList = _
            Application.WorksheetFunction.Transpose(UniqueItemList)
    End If
End If
On Error GoTo 0
End Function

```

- 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-39.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- 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-31.png)<figcaption>Run the Code</figcaption>

- Finally, you will receive the **output** as the **list box with unique values** from a Worksheet in Excel.

## Bottom-Line

We hope that this article gives you guidelines on how to  **fill a list box with unique values from a Worksheet 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/) **!!**

**Read Also:**

- **[How to Count Unique Values in a Column in Excel 365?](https://geekexcel.com/how-to-count-unique-values-in-a-column-in-excel-365/)**
- **[How to Extract Unique Values From A Column With Formula in Excel?](https://geekexcel.com/how-to-extract-unique-values-from-a-column-with-formula-in-excel/)**
- **[Excel Formulas to Highlight Unique Values ~ Easy Tricks!!](https://geekexcel.com/excel-formulas-to-highlight-unique-values/)**
- **[Excel Formulas To Count Unique Values In a Column!!](https://geekexcel.com/excel-formulas-to-count-unique-values-in-a-column/)**
- **[Highlight Unique Values in Selection using Macros in Excel 365!!](https://geekexcel.com/highlight-unique-values-in-selection-using-macros-in-excel-365/)**