Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?

[StargateFanFromWork]

I found a great piece of coding in the archives for taking care of filters
in protected workbooks. My difficulty lies in that the sheets all have
different names and I don't know how to code for all sheets in a workbook.

Here's the code to put in the workbook module:

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

I'm guessing that it's the "Sheet1" that is stopping this from working.
I've tried removing the "Sheet1", etc., but all I get are errors. Is there
a way to modify the above so that it works on any sheet?: Users will be
adding new ones in the future and they'll call them all sorts of things that
would be impossible to determine in advance so a generic bit of code would
work best.

Thank you! D

 

[Norman Jones]

Hi StargateFanFromWork,

Try:

'=============>>
Private Sub Workbook_Open()
Dim SH As Worksheet
Const PWORD As String = "ABC"

For Each SH In Me.Worksheets
With SH
.Protect Password:=PWORD, UserInterfaceOnly:=True
.EnableAutoFilter = True
End With
Next SH
End Sub
'<<=============

 

[Leith Ross]

Hello StargateFanFromWork,

Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub

Sincerely,
Leith Ros

 

[StargateFanFromWork]

How do I leave out the password, pls? I just do a generic, or whatever it's
called, protect on the worksheets without any name or anything. Would like
to leave it without a pw.

btw, is there no way to just modify the "Worksheets("Sheet1").Activate" so
that it doesn't have to take a worksheet name? Just curious. It seems much
simpler than this code below. (But then, what do I know? <g>)

Thanks!

 

[Norman Jones]

Hi StargateFanFromWork,

How do I leave out the password, pls? I just do a generic, or whatever
it's
called, protect on the worksheets without any name or anything. Would
like
to leave it without a pw.

'=============>>
Private Sub Workbook_Open()
Dim SH As Worksheet

For Each SH In Me.Worksheets
With SH
.Protect UserInterfaceOnly:=True
.EnableAutoFilter = True
End With
Next SH
End Sub
'<<=============

btw, is there no way to just modify the "Worksheets("Sheet1").Activate" so
that it doesn't have to take a worksheet name? Just curious. It seems
much
simpler than this code below. (But then, what do I know? <g>)

The above code does not activate any sheet (which should be unnecessary) and
no sheet names are used.

 

[StargateFan]

Hello StargateFanFromWork,

Private Sub Workbook_Open()
Dim I As Integer
For I = 1 To ThisWorkbook.Worksheets.Count
Worksheets(I).Activate
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect UserInterfaceOnly:=True
Next I
End Sub

Sincerely,
Leith Ross

Thank you so much to everyone who responded! I'll try these out
tomorrow at work. D

 

[StargateFanFromWork]

You're right. Sorry. This works fantastically well, I'm so pleased. The
only thing that happens is that there is a bit of a flicker as each sheet is
dealt with. You see each sheet pop up for a millisecond. But that's
perfectly fine.

All the sheets are protected but the ones that have filters now allow the
filters to work.

Thank you so much everyone. Appreciate it! D