Author: Oscar Cronquist Article last updated on January 08, 2019

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. 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

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

  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 to code 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. Click and hold with left mouse button on the userform, then drag with mouse to create the listbox.

Create text

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

Create CommandButtons

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

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
'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, _

'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

Download Excel file