How can you change autofilter with VBA?

VTTW

New Member
Joined
Mar 24, 2002
Messages
26
I have a userform, frmrelease, that has two comboboxes, cmbcontrolcode & cmbjobnum.

cmbjobnum is bound to sheet "release", column A. cmbcontrolcode is bound to column B.

Here's a mock table....
~~~~~~~~~~~~~~~~~~~~~~~
Column A Column B
123 AAA
456 BBB
123 CCC
456 DDD
123 EEE
456 FFF
~~~~~~~~~~~~~~~~~~~~~~

Now, what I need to accomplish is this. If cmbjobnum is "123", then only "AAA", "CCC" & "EEE" populate cmbcontrolcode. Likewise, if cmbjobnum is "456", then only "BBB", "DDD" & "FFF" populate cmbcontrolcode.

So far, this is what I have (thanks to Babytiger), but it's not working...

Selection.AutoFilter Field:=0, Criteria1:=Sheets("release").cmbjobnum.value

Any Ideas??

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi VTTW,

I haven't researched this carefully, but I think the problem is simply the Selection qualifier for the Autofilter object, which is supposed to be a worksheet. Give this a try:

With Worksheets("release")
.AutoFilter Field:=0, Criteria1:=.cmbjobnum.value
End With
 
Upvote 0
I've tried it, but I'm getting a run time error. Here's what I have....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
Private Sub UserForm_Activate()
Me.cmbjobnum = Main.MAINjobnumberlist
With Worksheets("release")
.AutoFilter Field:=0, Criteria1:=.cmbjobnum.Value
End With
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Can you see what would give a run time error??

Thanks for your input.
 
Upvote 0
Hi again VTTW,

I goofed. I thought you combobox was on the worksheet rather than a userform. So my statement

.AutoFilter Field:=0, Criteria1:=.cmbjobnum.Value

should have been

.AutoFilter Field:=0, Criteria1:=Me.cmbjobnum.Value
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top