Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Values of ComboBox fields and SQL | Excel Discussion (Misc queries) | |||
Combobox and data values | Excel Programming | |||
ComboBox Values | Excel Programming | |||
Filtering ComboBox | Excel Programming | |||
ComboBox Values | Excel Programming |