AutoFilter can't work after the worksheet is being protected!!!

[Jac]

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.

 

[steve_doc]

Cause of the error is your worksheet protection afaik
especialy if you are trying to paste some data into protected sheet

Easy solution - programme your macro to unprotect the sheet
Perform rest of code
Then protect the worksheet again

HTH

 

[Bob Umlas]

Have the code first unprotect the sheet, do its thing, then reprotect the
sheet.

 

[Jac]

Hi Bob,

Thanks for your help...... ; )
The problem solved now!!

But what is that Use AutoFilter option actually use for in protection???
Why even we selected the option the AutoFilter won't work also???

 

[Jac]

Hi Bob,

Thanks for you help........ : )
Now my problem has solved!!

Anyway, what actually that Use AutoFilter option is use for in protection??
Why even we selected the option but still can't use the AutoFilter during
the protection???

 

[Jac]

Hi Steve,

Thanks for your help.......... : )
my problem has solved now!!!

 

[Dave Peterson]

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]

Hi Dave,

Thanks for your feedback!!!

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