Autofilter protected sheet

[vecia]

Hello, I have used the code below to unlock the autofilter on
protected sheet in excel. I'm not very good at VBA, so could someon
tell me how to change it so that it works for all of the sheets in th
workbook instead of just 2005?

Thanks

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("2005")
If Not .AutoFilterMode Then
.Range("A4:C4").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Su

 

[dominicb]

Good evening vecia

Only two new lines are needed, and we can get rid of one.

Sub Workbook_Open()
'check for filter, turn on if none exists
For Each ws In ActiveWorkbook.Worksheets
With ws
If Not .AutoFilterMode Then
.Range("A4:C4").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next ws
End Sub

You no longer need to specify the spreadsheet, because they will all b
done, so that line has gone, but replaced by a For ... Next loop whic
will perform the action on each sheet in turn.

HTH

Dominic

 

[Jim Rech]

This this:

Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
With WS
If Not .AutoFilterMode Then .Range("A4:C4").AutoFilter
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next
End Sub

 

[Mike Fogleman]

Private Sub Workbook_Open()
Dim i As Long

For i = 1 To Worksheets.Count
'check for filter, turn on if none exists
If Worksheets(i).AutoFilterMode Then 'that is an i, not 1
GoTo 1
Else
Worksheets(i).Unprotect Password:="password"
Worksheets(i).Range("A4:C4").AutoFilter
End If
Worksheets(i).Protect Password:="password"
1:
Next i
End Sub

Mike F

 

[vecia]

Thanks everyone, these all do the job :cool