Using an existing autofilter in protected sheet (with code)

[JKG]

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!!

 

[Tom Ogilvy]

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