Populate listbox with unique distinct values [VBA]

This post demonstrates how to:

• Insert a button to your worksheet
• Assign a macro to the button
• Create a basic user form containing a listbox
• Populate the listbox with unique distinct values using a macro.

Create button (Form Control)

1. Select sheet "Invoice".
2. Press with left mouse button on "Developer tab" on the ribbon.
3. Press with left mouse button on "Insert" button.
4. Press with left mouse button on Button (Form Control).
5. Create button "View Invoice" on sheet "Invoice".

Create macro

The macro displayed in the image above is not used in this article.

1. Press Alt-F11 to open visual basic editor.
2. Press with left mouse button on Module on the Insert menu.
3. Copy and paste "Sub Button1_Press with left mouse button on" code below to code module.

Sub Button1_Press with left mouse button on()
  UserForm1.Show
End Sub

Assign macro to button

1. Press with right mouse button on previously created button.
2. Press with left mouse button on "Assign Macro...".
3. Select Button1_Press with left mouse button on() macro.
4. Press with left mouse button on OK.

Create userform

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on "Insert" tab.
3. Press with left mouse button on Userform

Create Listbox

1. Press with left mouse button on "Listbox" button on Toolbox
2. Press and hold with left mouse button on the userform, then drag with mouse to create the listbox.

Create text

1. Press with left mouse button on Label
2. Create text "Select invoice:" above listbox

Create CommandButtons

1. Press with left mouse button on "Commandbutton"
2. Create Command buttons
3. Edit text in command buttons

Userform vba

1. Press with right mouse button on "Userform1" in project window
2. Press with left mouse button on "View code"
3. Copy and paste code below into code window
4. Exit visual basic editor

'The following macro populates a listbox with unique distinct numbers from sheet "Invoice data"
Private Sub UserForm_Initialize()
'Dimensioning variables
Dim Test As New Collection
Dim rng As Variant
Dim Value As Variant

'Identify range in column A that contains source data of invoice numbers
rng = Sheets("Invoice data").Range("A2:A" & _
Sheets("Invoice data").Columns("A").Find("*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row)


'If error occurs continue with next value without halting the macro
On Error Resume Next
'Iterate through cell values one by one
For Each Value In rng
  'If the length of the value is larger than 0 then add value to collection Test
  'The collection returns an error if the value already is in the collection
  If Len(Value) > 0 Then Test.Add Value, CStr(Value)
    'Add value to listbox
    ListBox1.AddItem Value
  'If an error has occurred then remove the value from the listbox
  If Err Then ListBox1.RemoveItem Value
'Continue with next value in rng
Next Value
'If error occurs stop macro
On Error GoTo 0
'The ListIndex property sets the currently selected item using an index number from 0 to n. 
ListBox1.ListIndex = 0
End Sub

Get the Excel file

Populate-invoice-listbox1.xls

Invoice category

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]

Edit invoice data [VBA]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]

Select and view invoice [VBA]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]

Listbox category

Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]

Macro category

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Excel categories

Comments (5)