How can you change autofilter with VBA?

[VTTW]

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

[VTTW]

anyone??

thanks

[Damon Ostrander]

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

[VTTW]

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.

[Damon Ostrander]

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