Returning values from a sheet based on ComboBox values - VBA

[cunnin]

Hi,

I'm running into a silly question and am hoping to get some help!

I have a spreadsheet that has a list of Name, Region, Company, Date, and Notes.

I've created a user form with different comboboxes where the user can select a specific Name, Region, and Company (based on named ranges) from this list. This is all looking great so far!

Now, once the user has selected these values from the comboboxes, I'd like to have a button that says "Go" which will show a list on a seperate sheet of all notes associated with the users selections. It may be easier to show, so for example give the following data:

Salesperson	Region	Date	Company	Notes
John	US	8/1/2013	Apple	Met with CEO
Jane	US	8/1/2013	Google	Met with CFO
Sarah	US	8/3/2013	Nike	Analyst meeting
Jane	Europe	8/5/2013	Apple	Product Demo
John	Europe	8/1/2013	Apple	Meeting with team
John	US	8/3/2013	Nike	Met CEO

<tbody>
</tbody>

In the user form, if the user chooses John & US in the comboboxes, it will show all the meetings and notes on a separate sheet that John had in the US. If the user chooses only Europe, it will show all meetings that everybody had in Europe on a separate sheet.

It seems relatively simple to solve with VBA but I'm running into a wall! I hope I've explained this well enough

I would greatly appreciate some advice and help! Many thanks in advance!

 

[AlphaFrog]

This example uses AutoFilter on your data based on selections from the comboboxes. If a combobox has a selection, its respective column is filtered. The filter criteria is additive.

I'm not sure what you want to do with the data is filtered.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmbGo_Click()
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]With[/color] Sheets("Data").UsedRange [color=green]'worksheet with your data[/color]
        .AutoFilter [color=green]'Clear previous filter if any[/color]
        [color=green]'Filter Salesperson[/color]
        [color=darkblue]If[/color] ComboBox1.ListIndex > -1 [color=darkblue]Then[/color] .AutoFilter 1, ComboBox1.Value
        [color=green]'Filter Region[/color]
        [color=darkblue]If[/color] ComboBox2.ListIndex > -1 [color=darkblue]Then[/color] .AutoFilter 2, ComboBox2.Value
        [color=green]'Filter Date[/color]
        [color=darkblue]If[/color] ComboBox3.ListIndex > -1 [color=darkblue]Then[/color] .AutoFilter 3, ComboBox3.Value
        [color=green]'Filter Company[/color]
        [color=darkblue]If[/color] ComboBox4.ListIndex > -1 [color=darkblue]Then[/color] .AutoFilter 4, ComboBox4.Value
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[cunnin]

Hi,

Thanks so much!! Works perfectly!!

Quick question - if in each combobox I have an option called "All", which is the equivalent of not selecting anything specific in a combobox, how would I incorporate this in the code?

Thanks again, and sorry for the silly questions

Cheers!

 

[AlphaFrog]

Hi,

Thanks so much!! Works perfectly!!

Quick question - if in each combobox I have an option called "All", which is the equivalent of not selecting anything specific in a combobox, how would I incorporate this in the code?

Thanks again, and sorry for the silly questions

Cheers!

        [COLOR=green]'Filter Salesperson[/COLOR]
        [COLOR=darkblue]If[/COLOR] ComboBox1.ListIndex > -1 And ComboBox1.Value <> "All" [COLOR=darkblue]Then[/COLOR] .AutoFilter 1, ComboBox1.Value

 

[cunnin]

Awesome, thank you so much!!

 

[cunnin]

Thanks again for this - I just have one more last silly question

If my header rows are on Row 8 and I want to apply the autofilter to that header row, is the easiest way to go about this just to add this line to the above code, or is there a more elegant way to do this?

[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]Rows("8:8").Select[/FONT][/COLOR][FONT=Verdana] [/FONT]
[FONT=Verdana]Selection.AutoFilter[/FONT] [COLOR=#222222][FONT=Verdana]

Thank you again for your help, I greatly appreciate it!

 

[AlphaFrog]

It's not necessary or efficient to .Select a range to use it in VBA. You can just reference a range without selecting it. There are several ways to reference a range depending on the nature of your data and your needs.

This is one that may work for your situation. It just offsets the used range by 7 rows to start at row 8. It will include 7 blank rows below the used range, but that doesn't matter to the autofilter function.

[color=darkblue]With[/color] Sheets("Data").UsedRange.Offset(7) [color=green]'worksheet with your data[/color]

 

[cunnin]

Perfect - yeah, I figured using .Select for a range was not efficient. Thanks again and very good to know!!

Also, one small problem I'm having - in your code above, you showed me how to tackle the user choosing the "ALL" option, but unfortunately this doesn't seem to be working. When the user selects "ALL", based on the code from above, currently the filter does filter the given column, but then selects none of the criteria in that given column. Should I force it to "Select All" in that column instead? Is there a way to do this?

I'm also wondering what the best way to tackle the problem of a user selecting an intersection of filter criteria that doesn't actually exist (for example in the above demo example a user selecting "Sarah" and "Europe"). Should an error message pop up? Or should I use dependent lists? (These lists would then have to be dynamic though so this might be tough). What do you think the best way to tackle is?

Many many thanks again for all your help, I greatly appreciate it!

Cheers

 

[AlphaFrog]

Also, one small problem I'm having - in your code above, you showed me how to tackle the user choosing the "ALL" option, but unfortunately this doesn't seem to be working. When the user selects "ALL", based on the code from above, currently the filter does filter the given column, but then selects none of the criteria in that given column. Should I force it to "Select All" in that column instead? Is there a way to do this?

I'm confused as to what you mean. It filters it, but doesn't filter it?

You'll have to show your code as well. You may have made changes or done something different.

I'm also wondering what the best way to tackle the problem of a user selecting an intersection of filter criteria that doesn't actually exist (for example in the above demo example a user selecting "Sarah" and "Europe"). Should an error message pop up? Or should I use dependent lists? (These lists would then have to be dynamic though so this might be tough). What do you think the best way to tackle is?

That's up to you. Depends on what you want.

It would be easy to test if there were no matching records by just finding the last row (after the filter) and testing if it's greater than the header row.

Example:

[COLOR=darkblue]If[/COLOR] Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row = 7 [COLOR=darkblue]Then[/COLOR]
    MsgBox "No matching records."
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

 

[kmicicar]

Hi,

There is a nice piece of code there it might not work for me entirely.
I have similar problem with exception that i want the matching records to appear in listbox.
My userform contain only one combobox and one listbox, combobox is containing values from a sheet range A:J column A.
I need to be able to select one value through combobox and display all matches in listbox.
my code so far is this:
Option Explicit
Dim XRNG3 As Range
Dim foundcell As Range
Dim rData As Range


Private Sub ComboBox1_Change()
Me.ListBox1.ListIndex = Me.ComboBox1.ListIndex


End Sub


Private Sub UserForm_Initialize()
Dim XRNG3 As Range
Set XRNG3 = ActiveWorkbook.Sheets("EVENT-BASE").Range("A:J")
ComboBox1.Value = ""
ListBox1.Value = ""
ComboBox1.List = XRNG3.Columns(1).Value 'registration
Set foundcell = ActiveWorkbook.Sheets("EVENT-BASE").Range("A:A").Find(ComboBox1.Value)
Set rData = ActiveWorkbook.Sheets("EVENT-BASE").Range("A1").CurrentRegion
Me.ComboBox1.List = rData.Offset(1).Value
Me.ListBox1.ColumnCount = 10
Me.ListBox1.List = Me.ComboBox1.List
End Sub

i have tried also few different variations with no results, this one gives me all the records and just highlight first match in listbox from combobox selection and display all the data i have in my sheet.
i would be grateful if someone point me into correct direction.