I chose method one, and it worked beautifully. I did add a line to reprotect
the sheet after the End With. thank you so much for your help.

"OssieMac" wrote:

There are 2 methods of fixing this. One is to include unprotect and protect
code within the code being run. The other method is to use a separate macro
to invoke the protection and set the UserInterfaceOnly parameter. (I dont
think this can be done in the userinterface mode).

The second method you can delete the macro after setting the protection so
the password is not visible in the code but make sure you keep a copy of the
password somewhere.

Also note that there was a bug in the previous code. You will see a comment
where I added a line of code. It caused a problem if more than one filter was
set then later one of the filters was turned off.

Following code for first method

Private Sub Worksheet_Activate()
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim i As Long

Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

'Edit the password to your password.
ws3.Unprotect Password:="ossiemac"

'Ensure autofilter is invoked on Sheet3
With ws3
If Not .AutoFilterMode Then
.UsedRange.AutoFilter
End If
'Following line added to code to remove a bug
If .FilterMode Then ShowAllData
End With

With ws2
'Test if Autofilter invoked on sheet2
If .AutoFilterMode Then
'Test if an actual filter is set
If .FilterMode Then
With .AutoFilter
'Test each filter
For i = 1 To .Filters.Count
With .Filters(i)
If .On Then
If .Operator = 0 Then
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1
Else
ws3.AutoFilter.Range _
.AutoFilter _
Field:=i, _
Criteria1:=.Criteria1, _
Operator:=.Operator, _
Criteria2:=.Criteria2
End If
End If
End With
Next i
End With
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If
Else
If ws3.FilterMode Then
ws3.ShowAllData
End If
End If

End With

ws3.Protect _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFiltering:=True, _
UserInterfaceOnly:=True, _
Password:="ossiemac"

End Sub



Second Method.
Remove the protect and unprotect lines from the previous code and then run
the following code on its own to protect the worksheet. You can copy it into
the same sheet module as the other code, place the cursor anywhere in the sub
and press F5 to run it from the VBA editor.

Sub ProtectSheet3()
Worksheets("Sheet3").Protect _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFiltering:=True, _
UserInterfaceOnly:=True, _
Password:="ossiemac"
End Sub

--
Regards,

OssieMac