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

--
veci
-----------------------------------------------------------------------
vecia's Profile: http://www.excelforum.com/member.php...nfo&userid=211
View this thread: http://www.excelforum.com/showthread.php?threadid=46640

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

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=46640

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


--
Jim
"vecia" wrote in
message ...

Hello, I have used the code below to unlock the autofilter on a
protected sheet in excel. I'm not very good at VBA, so could someone
tell me how to change it so that it works for all of the sheets in the
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 Sub


--
vecia
------------------------------------------------------------------------
vecia's Profile:
http://www.excelforum.com/member.php...fo&userid=2117
View this thread: http://www.excelforum.com/showthread...hreadid=466407

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

Hello, I have used the code below to unlock the autofilter on a
protected sheet in excel. I'm not very good at VBA, so could someone
tell me how to change it so that it works for all of the sheets in the
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 Sub


--
vecia
------------------------------------------------------------------------
vecia's Profile:
http://www.excelforum.com/member.php...fo&userid=2117
View this thread: http://www.excelforum.com/showthread...hreadid=466407

Thanks everyone, these all do the job :cool

--
veci
-----------------------------------------------------------------------
vecia's Profile: http://www.excelforum.com/member.php...nfo&userid=211
View this thread: http://www.excelforum.com/showthread.php?threadid=46640