Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Use Autofilter in Protect mode

Posted on 2012-03-21
10
Medium Priority
?
237 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 2000 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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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