Autofilter in protected sheet
 

Hi all,

I have protected a sheet and autfilter doesn´t work.

Can anybody tell me how can I use autofilter in this case?.

(The sheet contains several locked columns and only one is unlocked
(writable), so I wanna filter this. But autofilter doesn´t work in any column)

Thanks a lot in advance

Hi Javier,

I have protected a sheet and autfilter doesn´t work.

pls. post your excel version.

starting with excel 2002 (xp) you can allow filters in protected
workbooks in the options of Extras/Protection/SheetProtection
(translated from german).

when using xl 2000 and older you need a macro including these lines to
activate outlining and autofilter before protecting the sheet:

ActiveSheet.EnableAutoFilter = True
ActiveSheet.EnableOutlining = True

regards

arno

Thanks Arno. My version is Excel 2000. Sorry but I execute both lines before
protecting the sheet and it doens´t work. Can you help me?

Thanks

"arno" wrote:

Hi Javier,

I have protected a sheet and autfilter doesn´t work.

pls. post your excel version.

starting with excel 2002 (xp) you can allow filters in protected
workbooks in the options of Extras/Protection/SheetProtection
(translated from german).

when using xl 2000 and older you need a macro including these lines to
activate outlining and autofilter before protecting the sheet:

ActiveSheet.EnableAutoFilter = True
ActiveSheet.EnableOutlining = True

regards

arno

Hi Javier,

Thanks Arno. My version is Excel 2000. Sorry but I execute both lines
before protecting the sheet and it doens´t work. Can you help me?

you have an error in your code. I use the following code to protect and
unprotect all my sheets:

Sub protectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With

'enable outlining and filter
For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).EnableOutlining = True
Sheets(i).EnableAutoFilter = True
Next

'blablabla whatsoever i do here

'protect sheets
For Each blattl In ActiveWorkbook.Sheets
blattl.Protect "mysecretpassword", True, True, True, True
Next
Sheets(1).Select
End Sub

and to unprotect:
Sub unprotectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With
For Each blattl In ActiveWorkbook.Sheets
With blattl
.Unprotect "mysecretpassword"
.Visible = True
.ScrollArea = ""
End With
Next
End Sub

regards

arno

Thanks Arno, it works!!

Regards

"arno" wrote:

Hi Javier,

Thanks Arno. My version is Excel 2000. Sorry but I execute both lines
before protecting the sheet and it doens´t work. Can you help me?

you have an error in your code. I use the following code to protect and
unprotect all my sheets:

Sub protectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With

'enable outlining and filter
For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).EnableOutlining = True
Sheets(i).EnableAutoFilter = True
Next

'blablabla whatsoever i do here

'protect sheets
For Each blattl In ActiveWorkbook.Sheets
blattl.Protect "mysecretpassword", True, True, True, True
Next
Sheets(1).Select
End Sub

and to unprotect:
Sub unprotectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With
For Each blattl In ActiveWorkbook.Sheets
With blattl
.Unprotect "mysecretpassword"
.Visible = True
.ScrollArea = ""
End With
Next
End Sub

regards

arno

Autofilter in protected sheet
 

Arno,
I used the code you provided with success but still have a couple of issues
to grapple with. Perhaps you can help. First, the Excel 2000 worksheet I am
protecting and desire that the autofilters still work contains validation
with dropdown lists. These allow changes which I didn't desire. Any
solutions? Secondly, after executing the code you provided, once I close the
workbook and reopen it, the autofilters no longer work. Any way around that
other than an auto-open macro that contains my pasword for all to see?
--
Greg


"arno" wrote:

Hi Javier,

Thanks Arno. My version is Excel 2000. Sorry but I execute both lines
before protecting the sheet and it doens´t work. Can you help me?

you have an error in your code. I use the following code to protect and
unprotect all my sheets:

Sub protectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With

'enable outlining and filter
For i = 1 To ActiveWorkbook.Sheets.Count
Sheets(i).EnableOutlining = True
Sheets(i).EnableAutoFilter = True
Next

'blablabla whatsoever i do here

'protect sheets
For Each blattl In ActiveWorkbook.Sheets
blattl.Protect "mysecretpassword", True, True, True, True
Next
Sheets(1).Select
End Sub

and to unprotect:
Sub unprotectmysheets()
With Application
.EnableCancelKey = xlDisabled
.ScreenUpdating = False
End With
For Each blattl In ActiveWorkbook.Sheets
With blattl
.Unprotect "mysecretpassword"
.Visible = True
.ScrollArea = ""
End With
Next
End Sub

regards

arno