Autofilters not working when sheet is protected


M

murkaboris

Hello:

I was wondering if there is a way to make the autofilters work while the
sheet is protected. Specifically I have created List of Values for certain
column and want to limit the users choosing from them and not be able to
change any other fields that are already typed in thus the protection.

The List of values seems to work fine with the protection but the
Autofilters are not working.
Is there a way to make them function while keeping the worksheets protected?

Please help.
Thank you.

Monika
 
Ad

Advertisements

R

Ron de Bruin

Hi murkaboris

Unprotect the worksheet
Turn Autofilter on
Protect the worksheet and in the protect dialog check "Use AutoFilter"

If you have a very old version of Excel and not see this option in the protect sheet dialog post back
 
M

murkaboris

Hello Ron:

Unfortunately I'm on the older version of Excel that doesn't have this option.
Please advise.
Thank you.
Monika
 
M

murkaboris

Hello Ron:

Thank you for the reference. Just one more quick question when you are
listing multiple worksheets in a workbook for the code do you know the proper
way to include multiple ones?

With Worksheets("CT", "MR") --- this comes out wrong so not sure what's the
right way. I'd like the code to run on all of the worksheets in my workbook.
"CT" and "MR" are two separate worksheets....

Thank you.
Monika
 
R

Ron de Bruin

Hi Monika

If you want to do it in all sheets

For Each Sh In ActiveWorkbook.Worksheets

Next Sh

Or for a few sheets

For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3"))

Next Sh


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
 
Ad

Advertisements

M

murkaboris

Hi Ron:

Still missing something, sorry for the trouble.
Now I'm getting the "Compile error: - invalid or unqualified reference".

Here is my code:
Private Sub Workbook_Open()
'check for filter, turn on if none exists

For Each Sh In ActiveWorkbook.Worksheets
Next Sh
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="ib", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

Please help.
Thank you.

Monika
 
D

Dave Peterson

Private Sub Workbook_Open()
'check for filter, turn on if none exists

For Each Sh In ActiveWorkbook.Worksheets
with sh
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="ib", _
Contents:=True, UserInterfaceOnly:=True
End With
Next Sh
End Sub
 
G

Gord Dibben

You have somehow mangles the code.

This revision seems to work.......assuming you have data in A1

Private Sub Workbook_Open()
'check for filter, turn on if none exists

For Each sh In ActiveWorkbook.Worksheets
With sh
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="ib", _
Contents:=True, UserInterfaceOnly:=True
End With
Next sh
End Sub


Gord Dibben MS Excel MVP
 
M

murkaboris

Thank you Dave, worked like a charm!
I have to remember to put the Next sh at the end instead of on top...

Thanks again!
Monika
 
Ad

Advertisements

D

Dave Peterson

I should have asked about this before...

Private Sub Workbook_Open()
'check for filter, turn on if none exists

For Each Sh In ActiveWorkbook.Worksheets
with sh
.unprotect password:="ib" "<-- don't you need this, too????
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="ib", _
Contents:=True, UserInterfaceOnly:=True
End With
Next Sh
End Sub
 
Ad

Advertisements


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