Code for multiple worksheets

P

Paul S

I am using excel 2000

How can I make the following code, saved in the This Workbook modul
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

Pau
 
D

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top