Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing dropdown arrows from a filter
Hi there I have used the AutoFilterMode = False in my code but for some
reason when I apply the filter to the date the arrows are still there - does anyone know where I may be going wrong (code is at the bottom if wish to look at that) Also I have noticed that when I bring up the userform I am using to filter it will always contain the information that people last searched by - is there anyway to set this so it always comes up as blank when the form is loaded? Sub filterISRisk() ' Application.ScreenUpdating = False filterIS.Show ISMonth = filterIS.cmbMonth.Value ISName = filterIS.cmbName.Value 'filterIS.Hide If ISName = "" & Worksheets("Sheet1").AutoFilterMode = True Then Worksheets("Sheet1").ShowAllData With Worksheets("Sheet1") ..Select ..AutoFilterMode = False If ISName < "" Then Range("A1").AutoFilter Field:=2, Criteria1:=ISName Range("A1").AutoFilter Field:=1, Criteria1:=ISMonth Else filterIS.Hide End If End With Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing dropdown arrows from a filter
You're hiding the userform so it will reappear just as it was. If you want
it reset the easiest way to achieve that is to unload it. Replace "filterIS.Hide" with "Unload filterIS" I didn't run your code but this: If ISName = "" & Worksheets("Sheet1").AutoFilterMode = True Then Is probably not what you want. An ampersand is used for concatenation only. I think you're trying to do a logical And. So replace the "&" with "And". Re the autofilter arrows remaining, if they go away after the macro runs and you do a PgDn/PgUp from the keyboard it is a redraw issue. If they don't it's a code issue. In the former case you might consider adding a macro page down/ page up: ActiveWindow.LargeScroll Down:=1 ActiveWindow.LargeScroll Down:=-1 -- Jim "Pasty" wrote in message ... | Hi there I have used the AutoFilterMode = False in my code but for some | reason when I apply the filter to the date the arrows are still there - does | anyone know where I may be going wrong (code is at the bottom if wish to look | at that) | | Also I have noticed that when I bring up the userform I am using to filter | it will always contain the information that people last searched by - is | there anyway to set this so it always comes up as blank when the form is | loaded? | | Sub filterISRisk() | ' | Application.ScreenUpdating = False | filterIS.Show | ISMonth = filterIS.cmbMonth.Value | ISName = filterIS.cmbName.Value | 'filterIS.Hide | If ISName = "" & Worksheets("Sheet1").AutoFilterMode = True Then | Worksheets("Sheet1").ShowAllData | With Worksheets("Sheet1") | .Select | .AutoFilterMode = False | If ISName < "" Then | Range("A1").AutoFilter Field:=2, Criteria1:=ISName | Range("A1").AutoFilter Field:=1, Criteria1:=ISMonth | Else | filterIS.Hide | End If | End With | Application.ScreenUpdating = True | | End Sub | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing dropdown arrows from a filter
Hi thanks for your help,
Its not a redraw issue as I tried what you recommended. I'll keep tinkering with the code and see what happens - if I can't figure it out I'll just leave it as it is - as it isn't a big deal. "Jim Rech" wrote: You're hiding the userform so it will reappear just as it was. If you want it reset the easiest way to achieve that is to unload it. Replace "filterIS.Hide" with "Unload filterIS" I didn't run your code but this: If ISName = "" & Worksheets("Sheet1").AutoFilterMode = True Then Is probably not what you want. An ampersand is used for concatenation only. I think you're trying to do a logical And. So replace the "&" with "And". Re the autofilter arrows remaining, if they go away after the macro runs and you do a PgDn/PgUp from the keyboard it is a redraw issue. If they don't it's a code issue. In the former case you might consider adding a macro page down/ page up: ActiveWindow.LargeScroll Down:=1 ActiveWindow.LargeScroll Down:=-1 -- Jim "Pasty" wrote in message ... | Hi there I have used the AutoFilterMode = False in my code but for some | reason when I apply the filter to the date the arrows are still there - does | anyone know where I may be going wrong (code is at the bottom if wish to look | at that) | | Also I have noticed that when I bring up the userform I am using to filter | it will always contain the information that people last searched by - is | there anyway to set this so it always comes up as blank when the form is | loaded? | | Sub filterISRisk() | ' | Application.ScreenUpdating = False | filterIS.Show | ISMonth = filterIS.cmbMonth.Value | ISName = filterIS.cmbName.Value | 'filterIS.Hide | If ISName = "" & Worksheets("Sheet1").AutoFilterMode = True Then | Worksheets("Sheet1").ShowAllData | With Worksheets("Sheet1") | .Select | .AutoFilterMode = False | If ISName < "" Then | Range("A1").AutoFilter Field:=2, Criteria1:=ISName | Range("A1").AutoFilter Field:=1, Criteria1:=ISMonth | Else | filterIS.Hide | End If | End With | Application.ScreenUpdating = True | | End Sub | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the colour of the 'filter arrows' | Excel Worksheet Functions | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
showing dropdown selection arrows in spread sheets | Excel Discussion (Misc queries) | |||
Filter arrows freezing! | Excel Worksheet Functions | |||
color of filter arrows | Excel Worksheet Functions |