Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) | |||
access to autofilter with protection | Excel Discussion (Misc queries) | |||
Using Autofilter with Protection on | Excel Worksheet Functions | |||
Using AutoFilter with worksheet protection in 2000 vs. 2003 | Excel Discussion (Misc queries) | |||
Sheet protection; can't use AutoFilter | Excel Discussion (Misc queries) |