Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
ronwill
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
ronwill
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Have you had a problem with Auto Filter freezing? Florida User Excel Discussion (Misc queries) 2 November 30th 05 06:56 PM
auto filter question Juco Excel Worksheet Functions 0 November 29th 04 03:48 PM
auto filter question Juco Excel Worksheet Functions 1 November 28th 04 03:51 PM
The Auto Filter button lost the column specified option. D Excel Worksheet Functions 1 November 5th 04 12:47 AM
Why can't my macro use Auto Filter when I told the Sheet Protecti. KC Rippstein Excel Worksheet Functions 1 October 28th 04 06:13 PM


All times are GMT +1. The time now is 07:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"