Excel VBA Listbox population based on criteria

[JasonEnsor]

Hey,

So i have found myself looking for VBA answers once more, hopefully someone can help me with this. I have some code to populate a VBA userform listbox with data from a workbook. However what i want to do is to populate the listbox with values only if a certain criteria is met.

On my userform i have a combobox with Class Times on, when i select a class time and hit search i would like my list box to populate with all the swimmer names where the rows contain that that class time selected.

The code below populates everything. Just can't seem to figure out how to filter it on extra criteria. I am guessing it's going to be something stupid that i am missing.

Any help would be appreciated.

Many Thanks

J.

Dim LastRow As Long
Dim SwimmerData As Range
'Clear Current List
RegisterList.Clear

With Worksheets("Swimmer Details")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

For Each SwimmerData In .Range("A2:A" & LastRow)
RegisterList.AddItem SwimmerData.Value
Next SwimmerData

End With

Application.ScreenUpdating = False

 

[SkipVought]

Hi,

SOP -- use a query, using the required criteria, record set as the row source for your list box.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Skip,

Thanks for the reply, Any chance you could give more insight in to how to do a query in excel vba

Many Thanks

Jason

 

[SkipVought]

You could use VBA or not.

VBA: use ADO to return a resultset

Excel: on a sheet, use MS Query, via Data > Get External Data > From Other Sources > Excel Files*... and drill down to your workbook, then in Add Tables select the Sheet/Table that contains your list and format a query, similar to what you might graphically do, in MS Access, adding whatever criteria you might need.

Due to it's simplicity, I'd suggest the latter. Once a querytable is added to a sheet, it can be simply REFRESHED when required. Zero to little VBA required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}