Autofilter set to "SHOW ALL" on open for a protected worksheet

[Guest]

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

 

[Paul B]

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

 

[Guest]

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,

 

[Gord Dibben]

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

 

[Guest]

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

 

[Guest]

Thanks Gord, but the workbook must be shared otherwise there is no point to
its existence.
In the advanced tab of the Share Workbook menu, there is a check box for
Filter Settings to include in personal view. Can anybody explain this
feature as it doesn't seem to make a difference whether is it checked or not.
The Excel Help doesn't say much about it, but from what I understand, it
should in theory solve my problem.
Thank you.

 

[Dave Peterson]

From xl2003's help:

Make any filter (filter: To display only the rows in a list that satisfy the
conditions you specify. You use the AutoFilter command to display rows that
match one or more specific values, calculated values, or conditions.) and print
settings you want for your personal use. Each user's settings are saved
individually by default.

If you want the filter or print settings made by the original author to be in
effect whenever you open the workbook, click Share Workbook on the Tools menu,
click the Advanced tab, and under Include in personal view, clear the Print
settings or Filter settings check box.

=====
It sounds like you may want to try clearing that checkbox.

Then you (as the author) could save the file with all the data shown. And then
go to a different user and have them filter, save, and see what happens when a
3rd user opens it.

 

[Guest]

Actually, that's exactly what I would need, because in fact every user needs
to see only the rows that relate to him (in this case the students in his
class) and is not concerned about everybody else's students. But I thought I
had tried what you said and it didn't make any difference whether it was
checked or not. Maybe I did it wrong. I will try again at work tomorrow as
I need multiple users. Thank you for your help. Much appreciated.

 

[Dave Peterson]

I tried it, too. But without multiple users, I'm not sure if my failed attempts
were valid.

 

[Guest]

Hi Dave,
No matter what I tried, check, uncheck, recheck, re-uncheck... the file
always opens on the filter of the last user. I don't know if I am doing
something wrong, but it doesn't want to cooperate for me. Thanks for your
help.
Sandra

 

[Dave Peterson]

Thanks for posting back.

Too bad it didn't work.

Hi Dave,
No matter what I tried, check, uncheck, recheck, re-uncheck... the file
always opens on the filter of the last user. I don't know if I am doing
something wrong, but it doesn't want to cooperate for me. Thanks for your
help.
Sandra