Auto filter and protection with many sheets

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi all,
The following procedure allows auto filter on protected sheets.
If I have many thus sheets, do I need to replicate all these lines or can I
include all their names somehow within this procedure?
With Worksheets("Tax Invoice records")
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Thanks,
Rob
 
If it's all the worksheets in the workbook, you won't need the names of any:

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks
.enableautofilter = true
.protect....
end with
next wks

(if all the passwords are the same, too!)

or if it's a subset of worksheets

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.enableautofilter = true
.protect....
end with
next wks

(same warning about the same password, too.)

I've seen some posts by Tom Ogilvy that say excel can get confused if this is in
your workbook_open event. Tom suggests selecting/activating the worksheet
before fiddling with the protection:

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.activate 'or .select
.enableautofilter = true
.protect....
end with
next wks
 
Hi Dave,
Just what I needed.
Thanks again.
Rob

Dave Peterson said:
If it's all the worksheets in the workbook, you won't need the names of any:

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks
.enableautofilter = true
.protect....
end with
next wks

(if all the passwords are the same, too!)

or if it's a subset of worksheets

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.enableautofilter = true
.protect....
end with
next wks

(same warning about the same password, too.)

I've seen some posts by Tom Ogilvy that say excel can get confused if this is in
your workbook_open event. Tom suggests selecting/activating the worksheet
before fiddling with the protection:

dim wks as worksheet
for each wks in worksheets(array("sheet1","sheet2","sheet3"))
with wks
.activate 'or .select
.enableautofilter = true
.protect....
end with
next wks
 
Back
Top