Removing dropdown arrows from a filter

G

Guest

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
 
J

Jim Rech

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
| 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
|
|
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top