Using Autofilter on a Protected Sheet

[nytwodees]

I am using Excel 2003.

I have a protected sheet that allows the user to enter quantities in only 1
column. All other cells are locked.

I have a control button that has code that uses the "Autofilter" statement.
This button filters out all blank rows or zero quantities that residein the
unlocked column.

When I click this control, I get the following Visual Basic error"

Run-time error '1004':
"You cannot use this command on a protected sheet..."

Is there any way that I can protect the sheet and accomplish the filtering
of the rows? Thanks in advance!

 

[Dave Peterson]

You could always unprotect the sheet, do the filter and then reprotect the
sheet.

If all your users (just you???) are using xl2003 (or higher), you can protect
the sheet:

Tools|Protection|Protect worksheet
and check the "User Autofilter" option (near the bottom).

 

[Gord Dibben]

Generally you have a procedure that unlocks the sheet, performs the filter, then
re-locks the sheet.

Sub filter_locked_sheet()

ActiveSheet.Unprotect Password:="justme"
'your filtering code
ActiveSheet.Protect Password:="justme"


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Using the Tools|Protection option won't allow your code to run.

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
'If .FilterMode Then
' .ShowAllData
'End If
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, but that
won't help when you're filtering via code.)

 

[nytwodees]

Hi Gord:

Thanks for your reply.

My code is as follows:

Private Sub CONDENSEButton_Click()
Rows("8:38").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveWindow.ScrollRow = 1
Range("A1:L1").Select
End Sub

I tried your suggestion, but could not get it to work. I'm sure the problem
is with how I surround your code with my code above. Your help would be
greatly appreciated!

 

[nytwodees]

Hi Dave:

Thanks for your replies.

I think what you suggested in your 1st reply is what Gord suggested in his
post. I'm not sure if your 2nd post negated your 1st post.

 

[Dave Peterson]

Protecting the sheet in code (using the userinterfaceonly parm) means that you
won't have to unprotect it, do the work, and reprotect in each of your macros.

 

[nytwodees]

Hi Dave:

By adding this line before my code fixes my problem:

ActiveSheet.Protect UserInterfaceOnly:=True

So this works:

Private Sub CONDENSEButton_Click()
ActiveSheet.Protect UserInterfaceOnly:=True
Rows("8:38").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveWindow.ScrollRow = 1
Range("A1:L1").Select
End Sub

Problem solved!

________________________________________________________________

However, I have another control button called "SHOW ALL ITEMS" that expands
the worksheet to its original size. I added this button in case the user
wants to make any revisions. Unfortunately, I receive the same error message
as before.

My original code was:

Private Sub EXPANDButton_Click()
'removes AutoFilter if one exists
AutoFilterMode = False
End Sub

This did not work to resolve the error.

So I tried this code:

Private Sub EXPANDButton_Click()
'removes AutoFilter if one exists
ActiveSheet.Protect UserInterfaceOnly:=True
AutoFilterMode = False
End Sub

This did not work either, same problem.

Any suggestion how I might fix this?

 

[Gord Dibben]

Private Sub CONDENSEButton_Click()
ActiveSheet.Unprotect Password:="justme"
Rows("8:38").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>somevalue"
ActiveWindow.ScrollRow = 1
Range("A1:L1").Select
ActiveSheet.Protect Password:="justme"
End Sub

"<>" doesn't seem to be any valid criterion as far as I can see so I added the
somevalue.

You may also want to experiment with Dave's "userfaceonly" code.


Gord

 

[Dave Peterson]

That line doesn't need to be included in the code that does the work.

It only needs to be set once. That's why I included it in the Auto_Open
procedure (placed in a general module).

 

[Dave Peterson]

"<>"
means the same as non-blank.
kind of
"<>"&""

 

[nytwodees]

Hi Dave:

As written in my previous post, that line (ActiveSheet.Protect
UserInterfaceOnly:=True) was not included in my original code for that
control. The same 1004 error occurs with or without that line.

In addition, you mention your module:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

1. What is the differnce between a module and a macro?
2. Where do you write it?
3. Is it necessary to accomplish what I want?
4. If I use this module, do I have to delete the "ActiveSheet.Protect
UserInterfaceOnly:=True" in my "Private Sub CONDENSEButton_Click()"?

 

[Gord Dibben]

Yep.

It sure does.....forehead a little tender about now<g>

Gord

 

[Dave Peterson]

You sure you didn't mean <>?

<hehehe>

Yep.

It sure does.....forehead a little tender about now<g>

Gord

<<snipped>>

 

[Dave Peterson]

There are several types of modules within your project.

You can create a general module by:
selecting your project
Insert|Module

Then paste the Auto_Open procedure in that module.

Change the name of the worksheet to what you need.
(Or is it really named Sheet1?)

And change the password to what you need, too.

Then delete that auto_Open procedure from where you originally put it.

====
There are worksheet modules.
I bet you put your CONDENSEButton_Click() procedure in one of those.

There are workbook modules. And class modules and Userform modules...

=======
#1. The module is the "holder" for the macro.

It's kind of like how you put formulas in a worksheet. The formula would
represent the macro, but the worksheet would represent the module.

#2. In that general module.

#3. Not necessary. Most things can be accomplished in lots of different ways.
But if you have several macros that change things on protected sheets, do you
want to add lines that unprotect and reprotect the worksheet each time?

#4. If you're going to use the UserInterfaceOnly:=true, then that line only
needs to be run once. I'd do it when the workbook opens (in Auto_Open).

If you don't want to use the Auto_Open procedure, then change your code to:

Hi Dave:

As written in my previous post, that line (ActiveSheet.Protect
UserInterfaceOnly:=True) was not included in my original code for that
control. The same 1004 error occurs with or without that line.

In addition, you mention your module:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

1. What is the differnce between a module and a macro?
2. Where do you write it?
3. Is it necessary to accomplish what I want?
4. If I use this module, do I have to delete the "ActiveSheet.Protect
UserInterfaceOnly:=True" in my "Private Sub CONDENSEButton_Click()"?

 

[nytwodees]

Hi Dave:

Thanks for your extensive help and time and patience!

My problem now is ONLY with my EXPAND control button. My CONDENSE control
button works fine.

I only need help with the EXPAND control button macro.

Dan

 

[Dave Peterson]

There are a few things that can't be done in code if you use the
userinterfaceonly parm. Removing the autofilter arrows is one of them:

Private Sub EXPANDButton_Click()
me.unprotect password:="whatyouwant"
me.AutoFilterMode = False
me.protect password:="whatyouwant"
End Sub

If you wanted to keep the arrows, but show all the data:

Private Sub EXPANDButton_Click()
me.unprotect password:="whatyouwant"
If me.FilterMode Then
me.ShowAllData
End If
me.protect password:="whatyouwant"
End Sub

 

[nytwodees]

Hi Dave:

"You're The Man!" Success at last.

Where did you learn this stuff!? Is there a book you would recommend?

Thanks again,

Dan

 

[Dave Peterson]

Glad it worked.

Depending on what you're doing, sometimes using userinterfaceonly makes sense.
Sometimes, embedding the unprotect and reprotect in each procedure makes sense.

And John Walkenbach's books are very good to start.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html