Author: Oscar Cronquist Article last updated on January 02, 2018

This post describes how to fill a listbox with unique values.

Create button (Form Control)

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

Create macro

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste "Sub Button1_Click" code below into module
Sub Button1_Click()
End Sub

Assign macro to button

  1. Right click previously created button
  2. Click "Assign Macro..."
  3. Select Button1_Click() macro
  4. Click OK

Create userform

  1. Press Alt-F11 to open visual basic editor
  2. Click "Insert" tab.
  3. Click Userform

Create listbox

  1. Click "Listbox" button on Toolbox
  2. Create listbox

Create CommandButtons

  1. Click "Commandbutton"
  2. Create Command buttons
  3. Edit text in command buttons

Create text

  1. Click Label
  2. Create text "Select invoice:"

Userform vba

  1. Right click "Userform1" in project window
  2. Click "View code"
  3. Copy and paste code below into code window
  4. Exit visual basic editor
Private Sub UserForm_Initialize()

'Populate listbox with unique invoice numbers from sheet "Invoice data"
Dim Test As New Collection
Dim rng As Variant
Dim Value As Variant

'Identify range
rng = Sheets("Invoice data").Range("A2:A" & _
Sheets("Invoice data").Columns("A").Find("*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _

'Filter unique values
On Error Resume Next
For Each Value In rng
  If Len(Value) > 0 Then Test.Add Value, CStr(Value)
    ListBox1.AddItem Value
  If Err Then ListBox1.RemoveItem Value
Next Value
On Error GoTo 0

ListBox1.ListIndex = 0

End Sub

Previous blog posts:

Invoice template with dependent drop down lists in excel
Excel vba: Save invoice data
Excel vba: Edit invoice data

Download excel sample file for this tutorial.
Populate invoice listbox.xls
(Excel 97-2003 Workbook *.xls)

Read part 2