Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter in protected sheet | Excel Worksheet Functions | |||
Protected Sheet and AutoFilter | Excel Programming | |||
Protected sheet and Autofilter? | Excel Programming | |||
Autofilter protected sheet | Excel Programming | |||
AutoFilter on a Protected sheet | Excel Programming |