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

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

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" wrote in message
...
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

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

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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=50512

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

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" wrote in message
...
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" wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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

On Wed, 25 Jan 2006 16:09:27 -0600, Leith Ross
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

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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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