Unique list in Listbox VBA

[lwarren30]

I have a data sheet that has multiple entries for Batch Number. Meaning a batch will have multiple expense entries but there will be only one batch number for those. Example: A receipt would be a batch and the items bought would be the entries. I want to create a userform with a listbox to display only the batch numbers (a unique list) in vba. I know how to do it with formulas but rather not because it requires more data.

How can I create a unique list of batch numbers and data related to it in a userform in VBA? And can this be sortable by date?

Thank you.

 

[mikerickson]

I wouldn't use a collection, I'd sort and insert directly. Adjust the range being looped through as needed.

Dim oneCell as Range, i as Long

With ListBox1
    For each oneCell in Range(Cells(1,1), Cells(Rows.Count,1).End(xlup))
        For i = 0 to .ListCount - 1
            If oneCell.Value < .List(i) Then
                .AddItem oneCell.Value, i
            ElseIf oneCell.Value = .List(i) Then
                Exit For
            End If
        Next i
        If i = .ListCount Then .AddItem oneCell.Value
    Next oneCell
End With

 

[Yongle]

This may not behave the way you want but hopefully gets you started - it is not clear when you wanted to sort by date\ batch number etc. Procedures kept small so that you can pick out and use the bits you need more easily (I hope!). Code below assumes that dates are in column "V"

Temporary worksheet
Instead of using arrays or collections, sorted and filtered data is copied to a temporary worksheet before being placed in listbox.(By using a worksheet it is easier to see what VBA is doing). Procedure to delete the temporary sheet included below but is not called.

Userform behaviour
2 listboxes are used lst_RB_Batches and lst_RB_BatchView
Listbox lst_RB_Batches is filled with a sorted list of unique batch numbers
The data is displayed in lst_RB_BatchView
Selecting a value in lst_RB_Batches refreshes lst_RB_BatchView to include only that batch

Declare these variables at top of a standard module:

Public wsData As Worksheet, wsTemp As Worksheet
Public rngFull As Range, rngData As Range

Userform code:

[I]Private Sub UserForm_Initialize[/I]()
    Call SetUp
    Call SortTheDataByDate
    Call SortTheDataByBatch
    Call GetUniqueBatchNumbers
    Call PopulateRB_BatchView
End Sub

[I]Private Sub SetUp[/I]()                                                           'remember to declare these variables as public in standard module
    Set wsData = Sheets("Financial_Input, Sheet12")
    Set rngFull = wsData.Range("A1").CurrentRegion
    Set rngData = rngFull.Offset(1).Resize(rngFull.Rows.Count - 1)
    Set wsTemp = Sheets.Add(After:=Sheets(Sheets.Count))   
End Sub

[I]Private Sub GetUniqueBatchNumbers[/I]()
    wsTemp.Cells.Clear
    rngFull.AutoFilter
    rngData.Offset(, 1).Resize(, 1).Copy wsTemp.Cells(1, 1)
    wsTemp.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    lst_RB_Batches.List = wsTemp.Cells(1, 1).CurrentRegion.Value
End Sub

[I]Private Sub lst_RB_Batches_Change[/I]()
    rngFull.AutoFilter Field:=2, Criteria1:=lst_RB_Batches.Value
    Call PopulateRB_BatchViewWithFilteredData
End Sub

[I]Private Sub PopulateRB_BatchViewWithFilteredData[/I]()
    wsTemp.Cells.Clear
    rngFull.SpecialCells(xlCellTypeVisible).Copy wsTemp.Cells(1, 1)
    lst_RB_BatchView.List = wsTemp.Cells(1, 1).CurrentRegion.Value
End Sub

[I]Private Sub SortTheDataByBatch[/I]()
    With rngFull
        .AutoFilter
        .Parent.Sort.SortFields.Clear
        .Sort key1:=.Cells(2, "B"), order1:=xlAscending, Header:=xlYes
    End With
End Sub

[I]Private Sub SortTheDataByDate[/I]()
    With rngFull
        .AutoFilter
        .Parent.Sort.SortFields.Clear
        .Sort key1:=.Cells(2, "V"), order1:=xlAscending, Header:=xlYes
    End With
End Sub

[I]Private Sub PopulateRB_BatchView[/I]()
    With lst_RB_BatchView
    .ColumnCount = rngFull.Columns.Count
    .List = rngFull.Value
End With
End Sub

[I]Private Sub DeleteTempSheet[/I]()
    Application.DisplayAlerts = False
        wsTemp.Delete
    Application.DisplayAlerts = True
End Sub

 

[lwarren30]

Thank you for the replies. I'll try when I get off later tonight.

Could I copy the data in the same sheet but to right of the data? To prevent the need of a new worksheet.

 

[Yongle]

The new worksheet is not within your control!
It will be temporary and can be hidden etc