Using an existing autofilter in protected sheet (with code)
 

Hi,

I've read a bunch of posts about a similar problem, but everything I read is
telling me that I should be able to do this - yet when I try, I get a run
time error every time.

I am using Excel 2003. In my spreadsheet, I have created an autofilter. It
is already in place. (I'm not creating a new one! I know you can't do that.)
Within this range being filtered, some cells are locked, some are not. I have
a button for "hide blank rows" and "show blank rows to enter new customer"
This button runs very simple code:

Selection.AutoFilter Field:=4, Criteria1:="<"

or without "Criteria1:="<" to show rows.

But when I protect the sheet - *even with choosing the allow AutoFilter
option* it still gives me the error:

Run-time error '1004':
You cannot use this command on a protected sheet. To unprotect the sheet,
use the Unprotect Sheet command (tools menu.......

I absolutely must be able to hide and show blank rows with the macro - I
don't trust the users to be able to navigate the autofilter on their own -
they're not Excel users, they're salespeople. But a big button that says
"hide" or "show" should be ok.

I even tried using code to "protect" the cells with a message box saying
"are you sure you want to change this formula?" and an option for "no" that
will undo the change, but the only way I can make that work is to tie it to
the Worksheet_Change event and trap only those changes where the Target is
within my protected range. But as soon as they click "no" to undo the change
- that changes the worksheet again and kicks off the "Are you sure you want
to change this?" again - which would confuse these people to no end.

HELP!

Thanks!!

Using an existing autofilter in protected sheet (with code)
 

This should work in all versions of Excel after xl97.

Private Sub CommandButton1_Click()
Dim rng As Range
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Password:="ABC", UserInterfaceOnly:=True
Set rng = ActiveSheet.AutoFilter.Range
rng.AutoFilter Field:=4, Criteria1:="<"
End Sub

Private Sub CommandButton2_Click()
Dim rng As Range
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Password:="ABC", UserInterfaceOnly:=True
Set rng = ActiveSheet.AutoFilter.Range
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End Sub

It worked fine for me (xl2003). My sheet was protected with a password of ABC

--
Regards,
Tom Ogilvy



"JKG" wrote:

Hi,

I've read a bunch of posts about a similar problem, but everything I read is
telling me that I should be able to do this - yet when I try, I get a run
time error every time.

I am using Excel 2003. In my spreadsheet, I have created an autofilter. It
is already in place. (I'm not creating a new one! I know you can't do that.)
Within this range being filtered, some cells are locked, some are not. I have
a button for "hide blank rows" and "show blank rows to enter new customer"
This button runs very simple code:

Selection.AutoFilter Field:=4, Criteria1:="<"

or without "Criteria1:="<" to show rows.

But when I protect the sheet - *even with choosing the allow AutoFilter
option* it still gives me the error:

Run-time error '1004':
You cannot use this command on a protected sheet. To unprotect the sheet,
use the Unprotect Sheet command (tools menu.......

I absolutely must be able to hide and show blank rows with the macro - I
don't trust the users to be able to navigate the autofilter on their own -
they're not Excel users, they're salespeople. But a big button that says
"hide" or "show" should be ok.

I even tried using code to "protect" the cells with a message box saying
"are you sure you want to change this formula?" and an option for "no" that
will undo the change, but the only way I can make that work is to tie it to
the Worksheet_Change event and trap only those changes where the Target is
within my protected range. But as soon as they click "no" to undo the change
- that changes the worksheet again and kicks off the "Are you sure you want
to change this?" again - which would confuse these people to no end.

HELP!

Thanks!!