Filtering Values using a Combobox
 

I have three values in a combobox:
Quant,Qual,Both

There is a list: 10 Quant,10 Qual and 10 Both
When the user chooses Quant, only Quant values are shown etc.

The below code is failing at ActiveSheet.ShowAllData

I'm trying the code below:

Sub ComboBox1_Click()
Call filt
End Sub


Sub filt()
If ComboBox1.Value = "Qual" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=2, Criteria1:="Qual"
Else
If ComboBox1.Value = "Quant" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=1, Criteria1:="Quant"
Range("a8").EntireRow.Hidden = True
Else
If ComboBox1.Value = "Both" Then
ActiveSheet.ShowAllData

Range("a8").EntireRow.Hidden = True
End If
End If
End If
End Sub

Filtering Values using a Combobox
 

Teresa-

ActiveSheet.ShowAllData only works when the data set is alrady filtered. If
the data set is displaying all of the data, it returns an error.

You can put the statement:

On Error Resume Next

under the "Sub filt()" line at the top of the procedure. This will tell the
sub to igonore the error and continue processing on the line after the one
that caused a problem.

Stan Shoemaker
Palo Alto, CA

"teresa" wrote:

I have three values in a combobox:
Quant,Qual,Both

There is a list: 10 Quant,10 Qual and 10 Both
When the user chooses Quant, only Quant values are shown etc.

The below code is failing at ActiveSheet.ShowAllData

I'm trying the code below:

Sub ComboBox1_Click()
Call filt
End Sub


Sub filt()
If ComboBox1.Value = "Qual" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=2, Criteria1:="Qual"
Else
If ComboBox1.Value = "Quant" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=1, Criteria1:="Quant"
Range("a8").EntireRow.Hidden = True
Else
If ComboBox1.Value = "Both" Then
ActiveSheet.ShowAllData

Range("a8").EntireRow.Hidden = True
End If
End If
End If
End Sub

Filtering Values using a Combobox
 

Another way is to check first:

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

teresa wrote:

I have three values in a combobox:
Quant,Qual,Both

There is a list: 10 Quant,10 Qual and 10 Both
When the user chooses Quant, only Quant values are shown etc.

The below code is failing at ActiveSheet.ShowAllData

I'm trying the code below:

Sub ComboBox1_Click()
Call filt
End Sub

Sub filt()
If ComboBox1.Value = "Qual" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=2, Criteria1:="Qual"
Else
If ComboBox1.Value = "Quant" Then
ActiveSheet.ShowAllData
Range("Letters").AutoFilter Field:=1, Criteria1:="Quant"
Range("a8").EntireRow.Hidden = True
Else
If ComboBox1.Value = "Both" Then
ActiveSheet.ShowAllData

Range("a8").EntireRow.Hidden = True
End If
End If
End If
End Sub

--

Dave Peterson