Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List, Data Validation, unlocked cell, protected sheet..... | New Users to Excel | |||
How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
Unable to open excel sheet in Protected mode from VB 6.0 | Setting up and Configuration of Excel | |||
how to hide rows in a protected sheet | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |