Excel vba: Populate listbox with unique invoice numbers
This post describes how to fill a listbox with unique values.
Create button (Form Control)
- Select sheet "Invoice"
- Click "Developer tab" on the ribbon
- Click "Insert" button
- Click Button (Form Control)
- Create button "View Invoice" on sheet "Invoice"
Create macro
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste "Sub Button1_Click" code below into module
Sub Button1_Click() UserForm1.Show End Sub
Assign macro to button
- Right click previously created button
- Click "Assign Macro..."
- Select Button1_Click() macro
- Click OK
Create userform
- Press Alt-F11 to open visual basic editor
- Click "Insert" tab.
- Click Userform
Create listbox
- Click "Listbox" button on Toolbox
- Create listbox
Create CommandButtons
- Click "Commandbutton"
- Create Command buttons
- Edit text in command buttons
Create text
- Click Label
- Create text "Select invoice:"
Userform vba
- Right click "Userform1" in project window
- Click "View code"
- Copy and paste code below into code window
- 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, _
LookIn:=xlValues).Row)
'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 SubPrevious blog posts:
Invoice template with dependent drop down lists in excel
Excel vba: Save invoice data
Excel vba: Edit invoice data
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
September 8th, 2011 at 9:54 am
Oscar, that's good usage of a collection to trap duplicates. How would you apply a sort using this same method?
Also, I use a Range("a1:a" & cells(rows.count,1).end(xlup).row) to be a more easier code to determine the last row.
September 8th, 2011 at 1:30 pm
chrisham,
How would you apply a sort using this same method?
I found this: Using a Visual Basic Macro to Sort Arrays in Microsoft Excel
Copy SelectionSort function into a standard module.
Userform1 vba code:
Private Sub UserForm_Initialize() 'Populate listbox with unique invoice numbers from sheet "Invoice data" Dim Test As New Collection Dim rng As Variant, temp() As Variant Dim Value As Variant, i As Single 'Identify range rng = Sheets("Invoice data").Range("A2:A" & _ Sheets("Invoice data").Columns("A").Find("*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row) ' Sort values ' rng = SelectionSort rng1 'Filter unique values On Error Resume Next For Each Value In rng If Len(Value) > 0 Then Test.Add Value, CStr(Value) End If Next Value On Error GoTo 0 ReDim temp(1 To Test.Count) For i = 1 To Test.Count temp(i) = Test(i) Next i SelectionSort temp For Each Value In temp ListBox1.AddItem Value Next Value ListBox1.ListIndex = 0 Set Test = Nothing End SubDownload excel file
Populate-invoice-listbox1_chrisham.xls
September 8th, 2011 at 2:37 pm
Thanks Oscar, I am keeping that Function SelectionSort(TempArray As Variant) in my personal library..... that's going to come handy quite few # of times in the future....appreciate your tutorials... Your resources always helps horn my Excel skills... Keep up the good work!