Excel VBA - Use Collection Object in Userform ListBox

Create a Userform with Listbox showing the unique items from available large data Excel.

Userform in Excel are very dynamic view and user friendly at user end. You can create a unique item list without removing duplicates from the column to show
in list box 

Using Collection as object to pass through each cell value and generating the unique list.

I have an example of an organization engaged in Sales of Items and they need to analyze the sales on Month, Quarter or Year basis. Applying simple tricks and with out manipulating the data, we can show analysis of Sales business.

Lets get started.

First Press F11 key on your keyboard to switch on VB Editor

Insert UserForm as shown below from Insert Menu or Alt IU

With this now you need to select List Box item from Tool Box and add on userform as shown in below image

Assign two list boxes one to show the list of month and the other to show list of Year.
Add one Text Box to show the total Amount
Add Label to indicate the month or year you select.
Apply the sensible name to List box objects which will be used in referencing to pass values into 
Final Form should look like this

Once you are done with User Form, right click on Userform and select View Code and initialize the form.


Let us play trick to get unique Month Name of Year from available data as shown in above image of excel sheet.


Create a Collection object in VBA

Declare Variables explicitly as shown in above image

In this example we would need to declare two variable as data type New Collection which will hold the unique values of Month and Year






Dim myList          As New Collection
Dim myYrList       As New Collection

Then declare variable to set the range that will be used in For - Each - Next Loop

               Dim myRng           As Range
               Dim mycell            As Variant
               Dim myVal            As Variant
               Dim myYrRng        As Range
               Dim myYrVal         As Variant
               Dim myYrCell        As Variant

Now set range 
    Set myRng = ws.Range("C3", ws.Range("C3").End(xlDown))
    Set myYrRng = ws.Range("A3", ws.Range("A3").End(xlDown))

Importantly, using collection object, write the code in For-Next loop

To capture Year from date column and pass in collection as string

        For Each myYrCell In myYrRng.Cells
            myYrList.Add _
                   Year(myYrCell.Value), CStr(Year(myYrCell.Value))
        Next myYrCell










To capture Month from Month Column and pass in collection as string
        For Each mycell In myRng.Cells
            myList.Add mycell.Value, CStr(mycell.Value)
        Next mycell

With this you are done with half of the coding. Now it is to show the unique values from collections into the list boxes in User Form

Pass the Values in ListBxYr from myYrList Collection

        For Each myYrVal In myYrList
            UserForm1.LtBxYr.AddItem myYrVal
        Next myYrVal

Pass values in Ltbx_Names from myList Collection, in which we have captured Month name

    For Each myVal In myList
        UserForm1.Ltbx_Names.AddItem myVal
    Next myVal

Finally Set Variables to nothing

Now we have initialized the form, next step is to coding the text boxes, List boxes

Above snippet is written for List box showing name of Month.







You can see how the ranges are declared and using 
Application.WorksheetFunction.SumIfs, we can get amount calculated and pass the total sum of the amount in TxtBxSum box.

On changing the values in list box, you will get the sum of amount in TxtBxSum text box


Further see VBA Code for list box showing Year.

Make sure you write code in proper sequence.

Finally design the TxtBxSum Text box as shown below.

Set TxtBxSum properties
Even you can show changing color of box on basis of the total sum value changes as per your need.

Add a button on spreadsheet and assign macro as Userform intialize


Once you done will all coding, it is time to test the execution of code without error
Press F8 to run in debug mode or F5 to execute.

See the result showing in userform

Hope this you found useful.














You can even download << Use Collection Object in Userform List Box >>
Practice Workbookon Google Drive.