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

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

CLR wrote:

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

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" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

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

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?

CLR wrote:

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" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

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

--

Dave Peterson

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" wrote in message
...
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?

CLR wrote:

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" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

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

--

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.


CLR wrote:

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" wrote in message
...
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?

CLR wrote:

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" wrote in message
...
Debra Dalgleish knows how:
http://contextures.com/xlautofilter03.html#Hide

CLR wrote:

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

--

Dave Peterson

--

Dave Peterson

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

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


"Jim Cone" wrote in message
...

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

Thanks for posting the clarification, Jim.

Jim Cone wrote:


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

--

Dave Peterson

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" wrote in message
...
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

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

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

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


"Jim Cone" wrote in message
...

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

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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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


"Jim Cone" wrote in message
...
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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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





"Jim Cone" wrote in message
...
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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)