?
Solved

Excel: Using Auto-Filter on Protected Sheet

Posted on 2003-03-03
6
Medium Priority
?
681 Views
Last Modified: 2009-12-16
I want to protect a worksheet but still allow AutoFilter on a data list.  (Yes, I too would rather do this in Access, but my audience knows Excel, so . . . here's my question.)

I'm creating a workbook template that includes quite a bit of VBA automation as well as some pretty complex formulas.  I want to protect the sheets, except for the cells that users will require for data entry.  I'm having trouble with one sheet, however, because I also want to allow the users to Auto-Filter the list of data they've entered.

Here are the specifics:  Users enter expense items in a prescribed range (A11:G1000) with column headings as follows: (A)Category, (B)Date, (C)Billable, (D)Cost, (E)Total [calculated field], (F)PO #, (G)Description.  In addition to the calculated column E, there are other fields on the sheet with complex formulas, and a couple of buttons that trigger VBA procedures.

I want to protect the sheet but still allow users to filter their data (all Travel expenses, for instance, or expenses between 2/1/2003 and 2/28/2003, or whatever they might think of).  Since I can't anticipate all of the questions they might have, Auto-Filters seem like the perfect solution, EXCEPT that the Auto-Filters stop working once I protect the sheet.

Is there a way to get the best of both worlds?  The only option I thought of was to add a button "Click here to AutoFilter" and create a procedure that would test for protection, unprotect if the sheet is protected, add the AutoFilter headings, and change the button's caption to "End AutoFilter."  If the sheet is already unprotected, the procedure would remove AutoFilter and re-protect the sheet.  That's a little too manual for my taste, though, and I'd like to find something more elegant.  Any ideas?
0
Comment
Question by:BarbMpls
[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
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:cri
ID: 8059099
For Excel 97 you can enable the Autofilter despite having the sheet protected. I can provide you the code if you want to.

For Excel 2000 (or is it XP?) you can choose this as option.
0
 
LVL 13

Accepted Solution

by:
cri earned 800 total points
ID: 8059147
Put this code in the ThisWorkbook object:


Private Sub Workbook_Open()
  'Enables the AutoFilter arrows on a protected worksheet named 'mySheet'.
  'Must be re-applied for each opening as property not saved
 
  With ActiveWorkbook.Sheets("mySheet")
    .EnableAutoFilter = True
    .Protect contents:=True, userInterfaceOnly:=True
  End With
End Sub
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 8069482
cri--

It must be XP that has the option setting, as I can't find it in Excel 2000.  

The code, however, is just what I wanted:  easy, elegant, and transparent to the users.

THanks for the quick answer.

Barbara
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 13

Expert Comment

by:cri
ID: 8070283
Thank you.

Perhaps of interest for you or eventual PAQ readers:

Displaying AutoFilter criteria  http://j-walk.com/ss/excel/usertips/tip044.htm

Further: The SUBTOTAL function is quite handy for filtered lists

a) Reduce likelyhood of mistaking a partial view for the global view
="Status of Table:" &IF(COUNTA(A2:A99)=SUBTOTAL(3,A2:A99)," All records"," (Partial List ONLY)")
 
b) Add visible cells only:
=SUBTOTAL(9,A2:A99)

See Excel help for details
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 8071603
Cri, you're the best!!  

I've just finished adding all three of those features to my workbook: AutoFilter Criteria, Filter Status, and Filtered Subtotals.  My users will be amazed!

Thanks again for the great assistance!
0
 
LVL 1

Author Comment

by:BarbMpls
ID: 8072338
Cri, you're the best!!  

I've just finished adding all three of those features to my workbook: AutoFilter Criteria, Filter Status, and Filtered Subtotals.  My users will be amazed!

Thanks again for the great assistance!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
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 video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

765 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