Nora Najem wrote:
Dear "davegb"...

Thank you for your reply, In fact I do not know how to use custom filter. I
would be delighted if you supply me with the code.

I tried the following solution.

I used this AutoOpen macro:

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.EnableAutoFilter = True
wSheet.Protect contents:=True, UserInterFaceOnly:=True
wSheet.Protect Password:="***"
Next wSheet
End Sub

This solution worked but it has a drawback which is when the workbook opens,
an unprotect messages will popup as many as the number of sheets.

What do you mean by "unprotect messages"?


My question is How to disable these popup messages in the same code ?.

Another drawback also is that when I unprotect a single sheet and, either
protect it manualy or leave it unproteced, then save and reopen the workbook
the AutoFilter will stop functioning unless I unproteced all the sheets and
close the workbook and open it again then I'll gain the functionality of
Autofiltering.

I don't understand that, but if we can fix the other problems, maybe
it'll take care of this one.


Your help would be much appreciated

Nora

Thank you again

Here is the code you requested:

Sub Showall()
Dim rStart As Range, rEnd As Range
Dim lRow As Long, lCol As Long

Set rStart = Range("B4")

lRow = rStart.End(xlDown).Row
lCol = Cells(lRow, Columns.Count). _
End(xlToLeft).Offset(, 2).Column

Set rEnd = Range(rStart, Cells(lRow, lCol))
rEnd.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=wksMacRec.Range("S5:Z6"), Unique:=False

End Sub

In this case, the region to be filtered starts at B4, and in order to
determine the filter range, I had to go across row 4 and down column B.
You can remove whatever code you don't need. The last line is the
filter. It uses the range "S5:Z6" for the filter criteria. The column
titles are there, but the criteria, which would be in row 6, are blank.
For some reason, this worked when nothing else did for removing the
filter on a protected sheet.


--
Microsoft Biased


"davegb" wrote:


Nora Najem wrote:
I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated


I had the same problem and figured out that if I applied a custom
filter with blank criteria, XL would show all of the data. I don't know
why the standard "Show All" doesn't work in this situation. I imagine
one of the experts here might know. If you want a copy of that macro,
let me know here and I'll post it.