Userforms and filtering

[Navvy]

Hello!

I have created a userform for the first time and require some help. Firstly, how do I add data to the drop down list? Secondly, based on this selection how would I filter for that data (which is in column A).

Thanks You!

 

[cass60660]

Combo3.RowSourceType = "Table/Query"
cmbBox1.RowSource = "SELECT name FROM table"
or
Combo3.RowSourceType = "Value List"
Combo3.RowSource = "1;2;3;4"

Success

 

[TonyJollans]

Hi Navvy,

In Excel, set the Row Source in the Properties Sheet or, in code ..

ComboBox1.RowSource = "A1:A6"

.. supplying your own name and range, of course.

Enjoy,
Tony

 

[VBAjedi]

You may find that you want to examine each value before adding it to your combobox (to filter out duplicates, etc). The following code adds items one at a time from a source range:

Combobox1.clear
For Each r In Range("A6:A1000")
   If rows(r.row).entirerow.visible = true and Len(r.Value) > 0 Then
      ComboBox1.AddItem r.Value
   End If
Next r

Now to filter your data when the user makes a selection in your Combobox (pretend your data is in A6:C1000):

Sub ComboBox1_Click()
   Sheets("Sheet1").Range("A6:C1000").Autofilter (Field:=1, criteria1:=ComboBox1.Value)
End Sub

Hope that helps!


VBAjedi

 

[Navvy]

Thank You! This seems what I need. But for some reason I can't get it working. Where should I put the code? I have named my drop down list TimPairs.

 

[VBAjedi]

Well, that depends on what you want to do. If you want the list to be filled once each time the form is opened, put the first bit of code in the UserForm_Initialize() event of your form. Or, to have it filled each time the user works with the form, you could put it in the UserForm_Activate() event.

You could also attach the code to a button on the form so the user could refresh the list anytime they want to.

The second bit of code goes in the ComboBox1_Click event.

Let us know if you get everything running!

VBAjedi

 

[Navvy]

Hi Jedi. Using your code I seem to get run time errors. I have put the code in UserForm_Initialize().

TimPair.Clear
For Each r In Range("A6:A1000&quot
If Rows(r.Row).EntireRow.Visible = True And Len(r.Value) > 0 Then
TimPair.AddItem r.Value
End If
Next r

TimPair is what I have named the listbox.

RunTime error 438. Object doesn't support this property or method.

 

[VBAjedi]

What line is giving you the error?

Assuming the listbox exists when the form is initialized and you are correctly referencing it, I would guess you might need an explicit reference to the worksheet. Try:

For Each r In Worksheets("MySheetName&quot.Range("A6:A1000&quot

If that doesn't work, let me know the specific line that is causing the error, and if it is the same error.

VBAjedi