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)
- Select sheet "Invoice".
- Press with left mouse button on "Developer tab" on the ribbon.
- Press with left mouse button on "Insert" button.
- Press with left mouse button on Button (Form Control).
- Create button "View Invoice" on sheet "Invoice".
Create macro
The macro displayed in the image above is not used in this article.
- Press Alt-F11 to open visual basic editor.
- Press with left mouse button on Module on the Insert menu.
- 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
- Press with right mouse button on previously created button.
- Press with left mouse button on "Assign Macro...".
- Select Button1_Press with left mouse button on() macro.
- Press with left mouse button on OK.
Create userform
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on "Insert" tab.
- Press with left mouse button on Userform
Create Listbox
- Press with left mouse button on "Listbox" button on Toolbox
- Press and hold with left mouse button on the userform, then drag with mouse to create the listbox.
Create text
- Press with left mouse button on Label
- Create text "Select invoice:" above listbox
Create CommandButtons
- Press with left mouse button on "Commandbutton"
- Create Command buttons
- Edit text in command buttons
Userform vba
- Press with right mouse button on "Userform1" in project window
- Press with left mouse button on "View code"
- Copy and paste code below into code window
- 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
Invoice category
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]
Listbox category
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. The list box […]
Macro category
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
5 Responses to “Populate listbox with unique distinct values [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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.
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:
Get the Excel file
Populate-invoice-listbox1_chrisham.xls
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!
Great stuff Oscar. You just saved me a few hours of dicking around!
Hi Oscar, found a bug, if there is only 1 row of data, then the filter will shows error. =)