Filtering & 'Show all' when w/book protected

[rugrat]

I have a workbook which is password protected and shared.

Users can use the filters set up across the top row, but often it'
hard to be sure that there isn't a filter in place. What would be grea
is if:

(1) the 'Show all' button worked when the book is protected. Why i
this disabled? It's not doing anything more than removin
the filter which the users _are_ able to do! At least the users coul
just hit the button and be sure that all filters are de-activated.

(2) Failing that - is there an alternative? e.g. a macro? Or is i
possible to change the colouring of the drop-down filter button fro
dark blue on grey to something which stands out more? e.g. Red o
white?

TI

 

[Dave Peterson]

I never understood why the builtin ShowAll button was disabled either.

Are you turning on that enableautofilter in code or via tools|protection|protect
sheet (in xl2002 or xl2003)?

If you're doing it in code, you can add a parm to your protect line that allows
a macro to do more things than users can. This setting isn't remembered between
closing/reopening the workbook. (Auto_open is a nice spot for it.)

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub

Then you could allow the users to click a button that runs a macro like this:

Sub myShowAll()
With ActiveSheet
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

And I don't think you can change those colors. (Remember that the row numbers
also change tint when a filter is active.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[AlfD]

Hi!

Debra Dalgleish http://www.contextures.com/tiptech.html has :


"Highlight AutoFilter Headings -- a User Defined Function, an
conditional formatting, highlight column headings where filters hav
been applied. FilterHighlight.xls 35 kb"

Al