Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel: Using Auto-Filter on Protected Sheet

Posted on 2003-03-03
6
Medium Priority
?
721 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

581 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