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

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" <(E-Mail Removed)> wrote in message
news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> > 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
>
>
>

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 <(E-Mail Removed)>
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" <(E-Mail Removed)> wrote in message
>> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
>> > 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
>>
>>
>>

"Gord Dibben" wrote:

> 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 <(E-Mail Removed)>
> 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" <(E-Mail Removed)> wrote in message
> >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>

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.

"Gord Dibben" wrote:

> 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 <(E-Mail Removed)>
> 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" <(E-Mail Removed)> wrote in message
> >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>

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.




Sandra wrote:
>
> 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.
>
> "Gord Dibben" wrote:
>
> > 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 <(E-Mail Removed)>
> > 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" <(E-Mail Removed)> wrote in message
> > >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >

--

Dave Peterson

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" wrote:

> 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.
>
>
>
>
> Sandra wrote:
> >
> > 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.
> >
> > "Gord Dibben" wrote:
> >
> > > 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 <(E-Mail Removed)>
> > > 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" <(E-Mail Removed)> wrote in message
> > > >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> > > >> > 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
> > > >>
> > > >>
> > > >>
> > >
> > >
>
> --
>
> Dave Peterson
>

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

Sandra wrote:
>
> 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" wrote:
>
> > 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.
> >
> >
> >
> >
> > Sandra wrote:
> > >
> > > 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.
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > 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 <(E-Mail Removed)>
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> > > > >> > 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
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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" wrote:

> I tried it, too. But without multiple users, I'm not sure if my failed attempts
> were valid.
>
> Sandra wrote:
> >
> > 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" wrote:
> >
> > > 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.
> > >
> > >
> > >
> > >
> > > Sandra wrote:
> > > >
> > > > 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.
> > > >
> > > > "Gord Dibben" wrote:
> > > >
> > > > > 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 <(E-Mail Removed)>
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > >> news:CBA44756-4143-416D-A8AF-(E-Mail Removed)...
> > > > > >> > 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
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>