Toggling that setting to allow autofiltering will allow the user to autofilter
an existing filter. But not your code.
You could unprotect the worksheet, do the work, reprotect the worksheet (like
others have said).
Or you could protect the worksheet and tell excel that you want to be able to
let your code do things, too, by using a special setting
(UserInterfaceOnly:=true, below):
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. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Jac wrote:
>
> Hi,
>
> I've recorded a macro which would help me to filter for desired records in a
> data list using MS Excel 2003. After that, I assigned the macro to a command
> button. But I wish to locked the location of the button on the worksheet, top
> (100), left (50). So I used Protect Sheet option to protect the sheet with
> Use AutoFilter option being selected. Unfortunately, once I run the macro
> after the sheet is protected; a debug will be generated, "Run-time error
> 1004"!!!
>
> What would be the cause for the debug???
> Anyone could help on this????
>
> Thanking in advance.
--
Dave Peterson
Similar Threads
