excel filter

P

Pedro

hi! I was trying to put a filter in a protected sheet in my workbook (Excel
2003)
It doesn't work. is there a way to put it working or it doesn't work at all?
thanks
 
A

Arvi Laanemets

Hi

You must have an Open event for workbook (in ThisWorkbook>General):

Private Sub Workbook_Open()
Sheets("YourSheetName").Unprotect Password:="YourPassword"
Sheets("YourSheetName").Protect Password:="YourPassword",
UserInterfaceOnly:=True
Sheets("YourSheetName").EnableAutoFilter = True
' repeat the code above for every sheet, you want to allow to be
filtered
End Sub
 
P

PS

thx.
i didn't explain myself the best way.
i added the filter in the unprotected sheet. then i protected it and now the
filter doesn't work.
i want to protect the sheet but leave the filter working..
can u help me resolving this question?

PS

"Harald Staff" escreveu:
 
D

Dave Peterson

If all your users are using xl2002+, you can protect the worksheet and allow the
autofilter to work (autofilter arrows are already applied).

It's an option near the bottom on the protect sheet dialog.

If any of your users are using xl2k or below, then you need a macro:

Use this in the Auto_open procedure in a General module:

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub

(you could use the workbook_open event under ThisWorkbook, too.)

This setting isn't remembered between closing/reopening the workbook. (So
Auto_open is a nice spot for it.)
 
P

PS

finally...
thx for your help..
now it works like I wanted...
thank you ...
carpe diem

Pedro Silva

"Dave Peterson" escreveu:
 
P

PS

i forgot to put the "use autofilter" active...
but thank you fo your help..

Pedro Silva

"Harald Staff" escreveu:
 
I

icsonu

Hello,
I didnt know how to insert a open event in a Workbook.
can u please help me out in applyng the following:
Private Sub Workbook_Open()
Sheets("YourSheetName").Unprotect Password:="YourPassword"
Sheets("YourSheetName").Protect Password:="YourPassword",
UserInterfaceOnly:=True
Sheets("YourSheetName").EnableAutoFilter = True
' repeat the code above for every sheet, you want to allow to be
filtered
End Sub
Click to expand...

Regards,
icsonu
 
Top