A combobox contains the following code:

Private Sub cbCAT_Change()
Filtercolumn = 12
If cbCAT < "" Then
Selection.AutoFilter Field:=Filtercolumn, Criteria1:="=" &
cbCAT
Else
Selection.auofilter Field:=Filtercolumn
End If

End Sub

Problem
When cbCAT < "" then a filter is set according to the value of cbCAT.
That works just fine.

When the user blanks the value and cbCAT = "" I want to set the filter
off. When I do it manually in the worksheet, it works fine. However,
then I do it here in the macro, I get an error message.

Anybody know whe?

Glen

Maybe explicitly specifying the worksheet would help:

If cbCat < "" Then
Worksheets("sheet1").AutoFilter.Range.AutoFilter _
Field:=FilterColumn, Criteria1:="=" & cbCat
Else
Worksheets("sheet1").AutoFilter.Range.AutoFilter Field:=FilterColumn
End If

=====
If that doesn't help...

Where is this combobox?
If it's on a worksheet, how do you populate it?
Do you use a linkedcell and/or listfillrange?


wrote:

A combobox contains the following code:

Private Sub cbCAT_Change()
Filtercolumn = 12
If cbCAT < "" Then
Selection.AutoFilter Field:=Filtercolumn, Criteria1:="=" &
cbCAT
Else
Selection.auofilter Field:=Filtercolumn
End If

End Sub

Problem
When cbCAT < "" then a filter is set according to the value of cbCAT.
That works just fine.

When the user blanks the value and cbCAT = "" I want to set the filter
off. When I do it manually in the worksheet, it works fine. However,
then I do it here in the macro, I get an error message.

Anybody know whe?

Glen

--

Dave Peterson

Dave,
Thanks for the help. That works great! What I don't understand is
why?
In the user form I select the sheet I want to filter during the form
activate event.

Anyway, it worked. Thanks again.

Glen

Dave Peterson wrote:
Maybe explicitly specifying the worksheet would help:

If cbCat < "" Then
Worksheets("sheet1").AutoFilter.Range.AutoFilter _
Field:=FilterColumn, Criteria1:="=" & cbCat
Else
Worksheets("sheet1").AutoFilter.Range.AutoFilter Field:=FilterColumn
End If

=====
If that doesn't help...

Where is this combobox?
If it's on a worksheet, how do you populate it?
Do you use a linkedcell and/or listfillrange?


wrote:

A combobox contains the following code:

Private Sub cbCAT_Change()
Filtercolumn = 12
If cbCAT < "" Then
Selection.AutoFilter Field:=Filtercolumn, Criteria1:="=" &
cbCAT
Else
Selection.auofilter Field:=Filtercolumn
End If

End Sub

Problem
When cbCAT < "" then a filter is set according to the value of cbCAT.
That works just fine.

When the user blanks the value and cbCAT = "" I want to set the filter
off. When I do it manually in the worksheet, it works fine. However,
then I do it here in the macro, I get an error message.

Anybody know whe?

Glen

--

Dave Peterson

Without seeing the code, it's difficult to say.

But I've always tried to avoid .select's and .activate's.

wrote:

Dave,
Thanks for the help. That works great! What I don't understand is
why?
In the user form I select the sheet I want to filter during the form
activate event.

Anyway, it worked. Thanks again.

Glen

Dave Peterson wrote:
Maybe explicitly specifying the worksheet would help:

If cbCat < "" Then
Worksheets("sheet1").AutoFilter.Range.AutoFilter _
Field:=FilterColumn, Criteria1:="=" & cbCat
Else
Worksheets("sheet1").AutoFilter.Range.AutoFilter Field:=FilterColumn
End If

=====
If that doesn't help...

Where is this combobox?
If it's on a worksheet, how do you populate it?
Do you use a linkedcell and/or listfillrange?


wrote:

A combobox contains the following code:

Private Sub cbCAT_Change()
Filtercolumn = 12
If cbCAT < "" Then
Selection.AutoFilter Field:=Filtercolumn, Criteria1:="=" &
cbCAT
Else
Selection.auofilter Field:=Filtercolumn
End If

End Sub

Problem
When cbCAT < "" then a filter is set according to the value of cbCAT.
That works just fine.

When the user blanks the value and cbCAT = "" I want to set the filter
off. When I do it manually in the worksheet, it works fine. However,
then I do it here in the macro, I get an error message.

Anybody know whe?

Glen

--

Dave Peterson

--

Dave Peterson