How to filter data before it is pulled into combobox/listbox AND remove data from listbox

NoSparks · #1 05-14-2019, 07:41 AM

For point 2, after determining arrUniqueCompanies you could try this to determine the latest date and what goes into the 'Daily List'

Code:

' Find latest 'date to be called' by filtering table and using subtotal
With Sheets("Data").ListObjects("tblMain").Range
    For i = LBound(arrUniqueCompanies) To UBound(arrUniqueCompanies)
            company = arrUniqueCompanies(i)
            'remove any existing filters
            .AutoFilter
            'filter for company
            .AutoFilter Field:=4, Criteria1:=company, Operator:=xlFilterValues
            'the latest date
            dateToBeCalled = WorksheetFunction.Subtotal(104, Sheets("Data").ListObjects("tblMain").ListColumns(8).DataBodyRange)
            'Populate the 'Daily Call' list box
            If dateToBeCalled <= Date Then
                DailyList.AddItem arrUniqueCompanies(i)
                DailyList.List(i, 1) = Format(dateToBeCalled, "dd-mmm-yy")
            End If
            'remove filter
            .AutoFilter
    Next i
End With

Izzii0x · #2 05-14-2019, 10:16 PM

Oh thank you (again)! This is great and works perfectly. I'm so impressed

1 questions though, my listbox is generated under userform_initialise, when I click sumbit or even close and reopen the userform, it hasn't updated the daily list (presumably because the userform is just hiding and showing not re-initialising), is there a way I can get a 'refresh' button or assign to the open userform button to do the same?

I.E. Company A is on daily call list for today, I call them and they ask for a callback in 2 weeks. When I submit, and maybe close and reopen the form, they are still in the call list until I save-close-reopen the workbook.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Listbox or Combobox population	wannaExcel	Excel Programming	4	04-06-2019 02:53 PM
Add-In creates ListBox from XLS, but data not saved in ppt	barbet	PowerPoint	0	05-26-2016 02:30 AM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox	CatMan	Excel Programming	14	08-18-2014 08:14 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox	CatMan	Excel	1	08-08-2014 09:41 AM
Data from Excel to Listbox multiselect to Word	marksm33	Word VBA	2	02-18-2014 08:30 AM