Sandra

There is no option to unprotect sheets in a shared workbook using VBA or
manually.

You will have to find another solution like not sharing the workbook?


Gord Dibben MS Excel MVP

On Sun, 16 Sep 2007 08:04:01 -0700, Sandra
wrote:

Thanks Paul,
It works perfectly as long as the Workbook is not shared. As soon as I
share it, I get a 1004 run-time error, unable to unprotect sheet. Any way to
change this? (When I protect the sheet, I select the select locked cells,
select unlocked cells, format cells and autofilter only) Do you know how to
put these to True as I am pretty clueless when it comes to VBA.
Thanks again,


"Paul B" wrote:

Sandra, give this a try,

Private Sub Workbook_Open()
Const PW As String = "123" 'Change Password Here



With Sheets("Sheet1") 'Change Sheet Name Here
If .FilterMode Then
.Unprotect Password:=PW
.ShowAllData
.Protect Password:=PW, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowFiltering:=True
End If
End With



End Sub



To put in this code, from your workbook right-click the workbook's icon and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor to the thisworkbook module, then, paste the code in the
window that opens on the right hand side, press Alt and Q to close this
window and go back to your workbook, now this will run every time you open
the workbook. You may have to change the macro security settings to get the
macro to run. To change the security settings go to tools, macro, security,
security level and set it to medium


You may also want to protect the VBA project so that someone can't see the
password there,

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
this will open the VBA editor, in Project Explorer right click on your
workbook name, if you don't see it press CTRL + r to open the Project
Explorer then select VBA project properties, protection, check lock project
for viewing and set a password. Press Alt and Q to close this window and go
back to your workbook and save and close the file. Be aware that this
password can be broken by third party software


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Sandra" wrote in message
...
Hi,
Searching through the discussion group I can see that several people seem
to
have problems with the autofilter show all feature. I have a protected
worksheet for a shared workbook. Although other users are able to
Autofilter, their autofilter setting is saved when they close the file.
Because the worksheet is protected, SHOW ALL is unavailable which means
that
the next user has to bring all the filters to ALL before being able to set
their own autofilter (which will be saved when they save the workbook).
It's
kind of annoying as a lot of users are VERY novice to Excel (2003). I
have
tried adding code to the on OPEN VBA as someone has suggested, but while
it
works well when the worksheet is not protected, it stops working when I
protect it. Does anyone know a way to solve that problem? Any help would
be
appreciated as I have run out of options.
Thanks
Sandra