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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFanFromWork - 25 Jan 2006 21:38 GMT

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

Reply to this Message

Norman Jones - 25 Jan 2006 21:59 GMT

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

>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
[quoted text clipped - 18 lines]
>
> Thank you! :oD

Reply to this Message

StargateFanFromWork - 25 Jan 2006 22:10 GMT

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!

> Hi StargateFanFromWork,
>
[quoted text clipped - 40 lines]
> >
> > Thank you! :oD

Reply to this Message

Norman Jones - 25 Jan 2006 22:22 GMT

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

> How do I leave out the password, pls? I just do a generic, or whatever
> it's
[quoted text clipped - 57 lines]
>> >
>> > Thank you! :oD

Reply to this Message

StargateFanFromWork - 26 Jan 2006 18:31 GMT

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

> Hi StargateFanFromWork,
>
[quoted text clipped - 90 lines]
> >> >
> >> > Thank you! :oD

Reply to this Message

Leith Ross - 25 Jan 2006 22:09 GMT

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

Reply to this Message

StargateFan - 26 Jan 2006 05:00 GMT

>Hello StargateFanFromWork,
>
[quoted text clipped - 9 lines]
>Sincerely,
>Leith Ross

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

Reply to this Message