autofilter and protection
 

I have a spreadsheet that is protected because of the formulas I have in the
cells so the ones using the sheet wont overwrite them and this works great
because the only cells actively used are the ones unlocked for use. We have
hundreds of entries in several different columns with headers. My question is
how can we autofilter without unprotecting the sheet and keeping the orginal
by having to save the filter, I do not want to loss the orginal sheet because
it contains a huge amount of info that could be lost if the filter is saved
verses save as. The majority of the ones using the sheet has NO computer
skills and we need to make it as simple as possible.

autofilter and protection
 

tankerman

From a posting by Dave Peterson....................................

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

End Dave post............................................]


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 16:31:00 -0800, tankerman
wrote:

I have a spreadsheet that is protected because of the formulas I have in the
cells so the ones using the sheet wont overwrite them and this works great
because the only cells actively used are the ones unlocked for use. We have
hundreds of entries in several different columns with headers. My question is
how can we autofilter without unprotecting the sheet and keeping the orginal
by having to save the filter, I do not want to loss the orginal sheet because
it contains a huge amount of info that could be lost if the filter is saved
verses save as. The majority of the ones using the sheet has NO computer
skills and we need to make it as simple as possible.

autofilter and protection
 

Gord, thanks for the help it works fine but while working with the sheets I
found that if I "autofilter" then protect the sheet I found that the
autofilter stays on the sheet and the sheet is protected as well. When I
autofilter column B and save I am still able to click the autofilter arrow
and select (ALL) and all of the entries in that column that was
autofiltered and saved comes back. Just luck in the order I did things.

"Gord Dibben" wrote:

tankerman

From a posting by Dave Peterson....................................

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

End Dave post............................................]


Gord Dibben MS Excel MVP

On Tue, 12 Dec 2006 16:31:00 -0800, tankerman
wrote:

I have a spreadsheet that is protected because of the formulas I have in the
cells so the ones using the sheet wont overwrite them and this works great
because the only cells actively used are the ones unlocked for use. We have
hundreds of entries in several different columns with headers. My question is
how can we autofilter without unprotecting the sheet and keeping the orginal
by having to save the filter, I do not want to loss the orginal sheet because
it contains a huge amount of info that could be lost if the filter is saved
verses save as. The majority of the ones using the sheet has NO computer
skills and we need to make it as simple as possible.