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

J

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.
 
S

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
 
J

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???
 
J

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???
 
D

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
 
J

Jac

Hi Dave,

Thanks for your feedback!!!


Dave Peterson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top