Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Filter - Protected sheet/workbook
I have created a spreadsheet that uses an auto filter. It works fine on my pc and on some others, however some friends say that whilst the auto filter box is visible if they select it nothing happens (no drop down list). The autofilter is in an unprotected cell, the worksheet/workbook is protected but not a shared wook book. One of the users gains access to the filters if he upprotects the workbook only. The problem does not seem to be related to version or operating system being run. Could it have something to do with a general setting in excel, or anyone else come accross a similar problem. Secondly (maybe related?) from the 'DATA' 'FILTER' menu in the protected sheets/workbook the autofilter is checked and greyed out but the advanced filter can still be selected. If one of the users selects the advanced filter it over rides the previous auto filter, then i need to unprotect the sheet and re-protect to use the autofilter again. How (if possible) can I prevent them selecting advanced filter to stop this problem occuring? Many thanks if someone can help.... Ron -- ronwill ------------------------------------------------------------------------ ronwill's Profile: http://www.excelforum.com/member.php...o&userid=30277 View this thread: http://www.excelforum.com/showthread...hreadid=499449 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Filter - Protected sheet/workbook
If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True End With End Sub It needs to be reset each time you open the workbook. (excel doesn't remember it after closing the workbook.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ronwill wrote: I have created a spreadsheet that uses an auto filter. It works fine on my pc and on some others, however some friends say that whilst the auto filter box is visible if they select it nothing happens (no drop down list). The autofilter is in an unprotected cell, the worksheet/workbook is protected but not a shared wook book. One of the users gains access to the filters if he upprotects the workbook only. The problem does not seem to be related to version or operating system being run. Could it have something to do with a general setting in excel, or anyone else come accross a similar problem. Secondly (maybe related?) from the 'DATA' 'FILTER' menu in the protected sheets/workbook the autofilter is checked and greyed out but the advanced filter can still be selected. If one of the users selects the advanced filter it over rides the previous auto filter, then i need to unprotect the sheet and re-protect to use the autofilter again. How (if possible) can I prevent them selecting advanced filter to stop this problem occuring? Many thanks if someone can help.... Ron -- ronwill ------------------------------------------------------------------------ ronwill's Profile: http://www.excelforum.com/member.php...o&userid=30277 View this thread: http://www.excelforum.com/showthread...hreadid=499449 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Filter - Protected sheet/workbook
Thanks Dave, I'll give that a try. It seems the reason for filters not working is related to the version of excel, only when the sheets are protected (unprotected works on Excel 97 to 2002). I am saving them as 97 versions to see if that works for the auto filter issue when the sheet is protected. -- ronwill ------------------------------------------------------------------------ ronwill's Profile: http://www.excelforum.com/member.php...o&userid=30277 View this thread: http://www.excelforum.com/showthread...hreadid=499449 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Filter - Protected sheet/workbook
I've never noticed any difference in behavior between xl97, xl2k, xl2002 and
xl2003. ronwill wrote: Thanks Dave, I'll give that a try. It seems the reason for filters not working is related to the version of excel, only when the sheets are protected (unprotected works on Excel 97 to 2002). I am saving them as 97 versions to see if that works for the auto filter issue when the sheet is protected. -- ronwill ------------------------------------------------------------------------ ronwill's Profile: http://www.excelforum.com/member.php...o&userid=30277 View this thread: http://www.excelforum.com/showthread...hreadid=499449 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have you had a problem with Auto Filter freezing? | Excel Discussion (Misc queries) | |||
auto filter question | Excel Worksheet Functions | |||
auto filter question | Excel Worksheet Functions | |||
The Auto Filter button lost the column specified option. | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |