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

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! :oD

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

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
'<<=============

---
Regards,
Norman


"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
>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! :oD
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> '<<=============
>
> ---
> Regards,
> Norman
>
>
> "StargateFanFromWork" <[email protected]> wrote in message
> news:[email protected]...
> >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! :oD

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.

---
Regards,
Norman


"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> '<<=============
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "StargateFanFromWork" <[email protected]> wrote in message
>> news:[email protected]...
>> >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! :oD
>
>

On Wed, 25 Jan 2006 16:09:27 -0600, Leith Ross
<[email protected]> wrote:

>
>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. :oD

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! :oD

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> ---
> Regards,
> Norman
>
>
> "StargateFanFromWork" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >> '<<=============
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >> "StargateFanFromWork" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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! :oD
> >
> >
>
>