Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Use Autofilter in Protect mode

Posted on 2012-03-21
10
Medium Priority
?
238 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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

916 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