Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering protected sheets (again).
Can we enable filtering more than once so that it still works even after any
toolbar command selected from the floating commandbar re-protects the sheet? I searched the archives yet again and found 2 messages again both with code kindly provided by Tom Ogilgy: Sub AllowFilter() ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterFaceOnly:=True End Sub Sub Auto_Open() With Worksheets("Sheet1") .EnableAutofilter = True .Protect UserInterfaceOnly:= True End With End Sub The Auto_Open prompted an idea. I've been using a variation of the first code above in the ThisWorkbook so that filtering is enabled upon opening the workbook. Yet the moment any of the commandbar codes are pressed the filtering then promptly is disabled since each has an "ActiveSheet.Protect" code at the end. [A corresponding unprotect code is put at the beginning of each macro so that sorting, etc., can be done. These two codes must remain.] So the goal is to find something that can be placed after that "ActiveSheet.Protect" line in each macro so that the user is allowed to manually filter the sheet without nullifying the ActiveSheet.Protect. That would be the crux of the matter, I'm guessing -- whether or not that can be done? TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering protected sheets (again).
Hi StargateFen,
Try removing the unprotect / re-protect instructions from the "commandbar codes". If you use the AutoOpen code from Tom. the sheet will remain protected but will enable VBA interaction with the sheet. For the user the sheet is protected but Vba is able to to handle the sheet as if it were not protected. --- Regards, Norman "StargateFanFromWork" wrote in message ... Can we enable filtering more than once so that it still works even after any toolbar command selected from the floating commandbar re-protects the sheet? I searched the archives yet again and found 2 messages again both with code kindly provided by Tom Ogilgy: Sub AllowFilter() ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect UserInterFaceOnly:=True End Sub Sub Auto_Open() With Worksheets("Sheet1") .EnableAutofilter = True .Protect UserInterfaceOnly:= True End With End Sub The Auto_Open prompted an idea. I've been using a variation of the first code above in the ThisWorkbook so that filtering is enabled upon opening the workbook. Yet the moment any of the commandbar codes are pressed the filtering then promptly is disabled since each has an "ActiveSheet.Protect" code at the end. [A corresponding unprotect code is put at the beginning of each macro so that sorting, etc., can be done. These two codes must remain.] So the goal is to find something that can be placed after that "ActiveSheet.Protect" line in each macro so that the user is allowed to manually filter the sheet without nullifying the ActiveSheet.Protect. That would be the crux of the matter, I'm guessing -- whether or not that can be done? TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering on a protected Sheet | Excel Discussion (Misc queries) | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Filtering for multiple sheets | Excel Discussion (Misc queries) | |||
Filtering a protected WS in a shared workbook - Excel 2000 | Excel Discussion (Misc queries) |