Code for multiple worksheets
 

I am using excel 2000

How can I make the following code, saved in the This Workbook module available to

either all worksheets in the workbook

or specifically an additional worksheet called "Commission Achievement" as well as the "Missing Data" worksheetin my workbook

The code allows filterring on protected sheets

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Missing Data")
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub


Thanks

Paul

Code for multiple worksheets
 

One way:

Option Explicit
Private Sub Workbook_Open()

Dim iCtr As Long
Dim WKSNames As Variant

WKSNames = Array("Missing data", _
"Commission Achievement")

For iCtr = LBound(WKSNames) To UBound(WKSNames)
With Me.Worksheets(WKSNames(iCtr))
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next iCtr
End Sub

If you really wanted all the worksheets in that workbook:

Option Explicit
Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In Me.Worksheets
With wks
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next wks
End Sub

Paul S wrote:

I am using excel 2000

How can I make the following code, saved in the This Workbook module
available to

either all worksheets in the workbook

or specifically an additional worksheet called "Commission Achievement"
as well as the "Missing Data" worksheetin my workbook

The code allows filterring on protected sheets

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Missing Data")
If Not .AutoFilterMode Then
Range("A3").AutoFilter
End If
EnableAutoFilter = True
Protect _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

Thanks

Paul

--
Paul S

--

Dave Peterson