Solved

Use Autofilter in Protect mode

Posted on 2012-03-21
10
236 Views
Last Modified: 2012-03-22
I am using Excel 2003.  I have followed instructions to use autofilter in a protected worksheet by clicking the appropriate box in the drop down (Tools/Protection/Protect Sheet, Allow All Users to Use/Autofilter).  Despite allowing Autofilter, it will not work when the worksheet is protected.
0
Comment
Question by:treeher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37747644
It only allows you to use an existing autofilter, not actually apply or remove an autofilter to the sheet.
0
 

Author Comment

by:treeher
ID: 37747701
To clarify, I have already activated autofilter in the worksheet prior to enabling protection.  The autofilter works fine in unprotected mode but will not work after protection is enabled.  My understanding is that allowing users to use autofilter in protect mode should have solved this, but the autofilter will not work after I enable protect.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37747788
Curious. What actually happens? Does the dropdown not work, or does the filter not apply, or do you get an error?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:treeher
ID: 37747861
You can click the Autofilter button and the drop down box opens up for the autofilter and you can select from it, for example Sort Ascending, but then nothing happens at all.  If I unprotect, it works fine.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37747894
Does the filtering (rather than sorting) work? Sorting is a separate setting.
0
 

Author Comment

by:treeher
ID: 37748080
AHA!  You got it.  Yes, the filtering works.  Now, how do I make the Sorting work?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 37748109
You have to check the Sort option in the protection dialog and you also have to unlock any cells that you want to be able to sort.
0
 

Author Comment

by:treeher
ID: 37748136
When I select the "Sort" option in  the Protection drop down and then protect the sheet, the autofilter still will not sort ... now I get the error message "The cell or chart you are trying to change is protected and therefore read only.  To modify a protected cell or chart, first remove protection using the Unprotect Sheet command."  If true, then of what use is it to allow "sort" for users?
0
 

Author Comment

by:treeher
ID: 37748149
OK, finally I have got it.  You have been a GREAT help to me!  Thank you.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37748190
My pleasure. :)
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question