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,

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

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

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

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

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

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