| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Autofilter
I'd like to display all of a list -or- only records with blank cells in the
8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
Try this:
Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
I had to modify it slightly to handle the "All" Case without generating
an error. Sub ToggleAutoFilter() If Not Range("H1").Parent.AutoFilter.Filters(1).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Charles Die_Another_Day wrote: Try this: Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
Dave
ToolsMacroRecord New Macro. Record yourself while doing the above. Gord Dibben MS Excel MVP On Mon, 28 Aug 2006 13:12:01 -0700, Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
Perhaps I've omited some critical info.
I'm running Excel 2000 on WinXP. My header row is B4:I4. Autofilters are on. (Visible) I've copied the code below changing "H1" to "I4" When run, it errors on the 1st line 'If Range...' Displays "Application-Defined or Object-Defined error" Any suggestions? Thanks. "Die_Another_Day" wrote: Try this: Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
It looks like you used the first code I posted, I caught my mistake and
reposted. Here it is again: Sub ToggleAutoFilter() If Not Range("H1").Parent.AutoFilter.Filters(1).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Try that version and let me know if you still have issues. Charles Dave wrote: Perhaps I've omited some critical info. I'm running Excel 2000 on WinXP. My header row is B4:I4. Autofilters are on. (Visible) I've copied the code below changing "H1" to "I4" When run, it errors on the 1st line 'If Range...' Displays "Application-Defined or Object-Defined error" Any suggestions? Thanks. "Die_Another_Day" wrote: Try this: Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
It needs to be Filters(8).On.
Try this: Sub ToggleAutoFilter() If Not Sheet1.AutoFilterMode Then Sheet1.Range("H1").AutoFilter End If If Not Range("H1").Parent.AutoFilter.Filters(8).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Mike F "Die_Another_Day" wrote in message ups.com... It looks like you used the first code I posted, I caught my mistake and reposted. Here it is again: Sub ToggleAutoFilter() If Not Range("H1").Parent.AutoFilter.Filters(1).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Try that version and let me know if you still have issues. Charles Dave wrote: Perhaps I've omited some critical info. I'm running Excel 2000 on WinXP. My header row is B4:I4. Autofilters are on. (Visible) I've copied the code below changing "H1" to "I4" When run, it errors on the 1st line 'If Range...' Displays "Application-Defined or Object-Defined error" Any suggestions? Thanks. "Die_Another_Day" wrote: Try this: Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
This version doesn't error but the If statement continues to return True
so that it never reaches the Else clause. "Die_Another_Day" wrote: It looks like you used the first code I posted, I caught my mistake and reposted. Here it is again: Sub ToggleAutoFilter() If Not Range("H1").Parent.AutoFilter.Filters(1).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Try that version and let me know if you still have issues. Charles Dave wrote: Perhaps I've omited some critical info. I'm running Excel 2000 on WinXP. My header row is B4:I4. Autofilters are on. (Visible) I've copied the code below changing "H1" to "I4" When run, it errors on the 1st line 'If Range...' Displays "Application-Defined or Object-Defined error" Any suggestions? Thanks. "Die_Another_Day" wrote: Try this: Sub ToggleAutoFilter() If Range("H1").Parent.AutoFilter.Filters(1).Criteria1 = "=" Then Range("H1").AutoFilter Field:=8 Else Range("H1").AutoFilter Field:=8, Criteria1:= "=" End If End Sub Place a button on your sheet from the Forms toolbar and choose the macro "ToggleAutoFilter" to run when clicked. Charles Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
Thanks, but I'm looking for an If Statement that would allow a button to
toggle the filter on/off. "Gord Dibben" wrote: Dave ToolsMacroRecord New Macro. Record yourself while doing the above. Gord Dibben MS Excel MVP On Mon, 28 Aug 2006 13:12:01 -0700, Dave wrote: I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
It needs to be Filters(8).On.
Try this: Sub ToggleAutoFilter() If Not Sheet1.AutoFilterMode Then Sheet1.Range("H1").AutoFilter End If If Not Range("H1").Parent.AutoFilter.Filters(8).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Mike F "Dave" wrote in message ... I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle Autofilter
Thanks very much to all for the help!
It's working well. "Mike Fogleman" wrote: It needs to be Filters(8).On. Try this: Sub ToggleAutoFilter() If Not Sheet1.AutoFilterMode Then Sheet1.Range("H1").AutoFilter End If If Not Range("H1").Parent.AutoFilter.Filters(8).On Then Range("H1").AutoFilter Field:=8, Criteria1:="=" Else Range("H1").AutoFilter Field:=8 End If End Sub Mike F "Dave" wrote in message ... I'd like to display all of a list -or- only records with blank cells in the 8th column. What code could I assign to a button that would toggle an autofilter? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
| 2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
| Autofilter toggle problem | Excel Programming | |||
| Pesky Autofilter Toggle | Excel Programming | |||
| Pesky Autofilter Toggle | Excel Programming | |||
Linear Mode
