Turn of Autofilter Arrows?

[CLR]

Hi All........
Anyone know if it's possible to programmatically turn off the DownArrows on
the display while leaving the Autofilter in it's filtered state?.....
I would like for my user to see only the Autofiltered results, and not be
aware that they can reorder them with the downarrows.
Otherwise, can I call my autofilter with code, then freeze it so the
downarrows do not work manually until I release them with more code.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

 

[CLR]

That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97

the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I think it could be lots of things--from worksheet protection to what c is.

Maybe more code and more description about your data--did you apply filters
already or what?

 

[Jim Cone]

Hi Chuck,
The "VisibleDropDown" option is not available in XL97 for the AutoFilter Method.
I don't know how to work around that at the present.
Regards,
Jim Cone



"CLR"
wrote in message
That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97
the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks for the info Jim........although it "does" make the arrows go away,
it's just that I get the error message and have to click it off.........

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Here's the whole code.


Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column <> 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to the
errant part and gives the error message, then when I click "Debug" it goes
ahead and hides the arrows...........but leaves me hung up on that line of
code.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Ok guys, I think I got it now............this seems to work

Sub HideArrows()
Dim c As Range
Dim i As Integer
On Error Resume Next
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False
For Each c In Range(Cells(1, 1), Cells(1, i))
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
Next
Application.ScreenUpdating = True
End Sub

Thanks for all your responses.........

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Well, I "thought" it was working during testing, but when I worked it in I
found that it did make the arrows go away, but also turned off the whole
Autofilter.........bummer. Guess I'll have to move up a notch in Excel
versions.

Thanks anyway to all
Vaya con Dios,
Chuck, CABGx3

 

[Jim Cone]

Chuck,
In post XL 97 versions, it appears you can remove individual arrows from
the AutoFilter range. However, if you remove the arrow on the filtered field
the entire range is displayed again.
Regards,
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"CLR"
wrote in message
Well, I "thought" it was working during testing, but when I worked it in I
found that it did make the arrows go away, but also turned off the whole
Autofilter.........bummer. Guess I'll have to move up a notch in Excel
versions.
Thanks anyway to all
Vaya con Dios,
Chuck, CABGx3



"CLR"
wrote in message

 

[CLR]

Thanks Jim......
Yeah, that's where I'm at now on 97..........bummer if it can't be on any
version.........guess I'll have to cheat then.....<g>

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I don't remember enough about xl97 to know about visibledropdown not being
there.

But in xl2k+ you could use this to be more specific about the range:

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Long
Application.ScreenUpdating = False

i = 0
For Each c In activesheet.autofilter.range.rows(1).cells
i = i + 1
if i <> 2 then
c.AutoFilter Field:=i, Visibledropdown:=False
End If
Next c

Application.ScreenUpdating = True
End Sub

Then you don't have to worry about what row and what column the autofilter range
starts.

Here's the whole code.

Sub HideArrows()
'hides all arrows except column 2
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column <> 2 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

If I have Autofilter applied and then run this macro, it gets down to the
errant part and gives the error message, then when I click "Debug" it goes
ahead and hides the arrows...........but leaves me hung up on that line of
code.

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Thanks for posting the clarification, Jim.

Hi Chuck,
The "VisibleDropDown" option is not available in XL97 for the AutoFilter Method.
I don't know how to work around that at the present.
Regards,
Jim Cone

"CLR"
wrote in message
That's cool Dave, thanks, it basically works, but I have a little problem
with it in my XL97
the following returns an error of "1004 Autofilter method of range class
failed"

c.AutoFilter Field:=c.Column, _
Visibledropdown:=False

Any idea how to fix?
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks Dave, but it does the same thing about killing the Autofilter when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g>

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

The code wasn't an attempt to get by the arrows showing up (or not showing up),
it was just to show you a way not to have to rely on the data being in A1
through some column in row 1.

Maybe you could add a row under the header row -- keep it empty. Then apply the
filter to that row and the data below. And hide that empty row with the arrows.

Thanks Dave, but it does the same thing about killing the Autofilter when it
kills the arrow on the filtered column..........guess I'll just have to
cheat <g>

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

NOW THATS WHAT I'M TALKING ABOUT!!!!!

A good old-fashioned "cheat".
I was gonna overlay the arrow row with a picture of itself, but this is a
lot easier.

Thanks Dave, that'll do me.

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I hate inserting blank rows. It just ruins the table structure of the data (I
think).

But if you really can't have, er, don't want arrows, why not?

NOW THATS WHAT I'M TALKING ABOUT!!!!!

A good old-fashioned "cheat".
I was gonna overlay the arrow row with a picture of itself, but this is a
lot easier.

Thanks Dave, that'll do me.

Vaya con Dios,
Chuck, CABGx3

 

[Jim Cone]

Chuck,
Found another way to do it. Dave's solution of hiding the row is
probably easier, but you can Delete the arrows and retain the filtering.
If you don't have any other shapes on the sheet then this works ...
'--
Sub ss()
Dim shp As Excel.Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub
'--

'Show the arrows again (no filtering)...
Sub sss()
ActiveSheet.Range("A5").AutoFilter '(specify the top left cell)
End Sub

 

[CLR]

Thanks Jim........

I've just run out of gas and am heading for the sack........I'll give it a
go tomorrow.

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Jim......
I appreciate that you keep trying, and came up with this unique method. If
the hiding-row thing were not so easy, I would definately go with this one
of yours over my "put a picture on top" method. The biggest problem is that
I do have several other shapes on that sheet that I would have to do a
different way. So, I guess in the long run, I'll stick with the hidden-row
thing.

Thanks so much for your efforts......they will probably be useful another
day.

Vaya con Dios,
Chuck, CABGx3