Hi Heidi
Copy the code below into the "ThisWorkbook" module, not a general module.
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect Password:="pass"
If ws.Name = "SheetName" Then ws.EnableAutoFilter = True
ws.Protect Password:="pass", userinterfaceonly:=True
Next ws
End Sub
--
XL2002
Regards
William
(e-mail address removed)
| What I want to accomplish is to share a workbook with other users that
| has all data protected, but AutoFilters enabled so that they can
| filter the spreadsheet as desired but not make changes to data. I
| don't want the users to have to unprotect and reprotect a sheet to do
| this - it should not be apparent to them that the sheets are
| protected, except if they try to make a change. I'm not sure what
| userinterface code I need to use, as I'm a novice with VB. What
| additions to the code I've used so far need to be made in order to
| make this happen? Thanks again!
|
| > Heidi,
| >
| > I may be wrong, but I think your original code was attempting to
| > ".EnableAutoFilter = True" on a protected worksheet. Given that
| > "userinterfaceonly: =True" has to be set each time a workbook is opened,
| > your code will fail.
| >
| > In other words, when you open the workbook, you should unprotect each
sheet
| > and then reprotect with the appropriate userinterface code.
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | Thanks, that code works, but I still seem to be missing something.
| > | What can be done to keep the EnableAutoFilter from resetting? Is there
| > | a workaround for that element?
| > |
| > |
| > | > | > Hi Heidi
| > | >
| > | > Try...
| > | >
| > | > Sub Auto_Open()
| > | > With Worksheets("SheetName")
| > | > .Unprotect Password:="pass"
| > | > .EnableAutoFilter = True
| > | > .Protect Password:="pass", userinterfaceonly:=True
| > | > End With
| > | > End Sub
| > | >
| > | > --
| > | > XL2002
| > | > Regards
| > | >
| > | > William
| > | >
| > | > (e-mail address removed)
| > | >
| > | > | > | > | I'm using Excel 2000 and am looking for a workaround to how sheet
| > | > | protection prevents the use of AutoFilters. I've checked the
answers
| > | > | to previous posts in the various Excel newsgroups, and none of the
VB
| > | > | code provided seems to be working for me. I think I might be
missing a
| > | > | step. If you apply the VB code to the worksheet, do you also need
to
| > | > | enable protection through Tools>Protection? This is the code from
the
| > | > | worksheet - is there something missing? Thanks for all help in
| > | > | advance.
| > | > |
| > | > |
| > | > | Sub Auto_Open()
| > | > | With Worksheets("SheetName")
| > | > | .Protect Password:="pass", userinterfaceonly:=True
| > | > | .EnableAutoFilter = True
| > | > | End With
| > | > |
| > | > | End Sub
