I am using the subs below to run an advanced filter filtering data from another sheet.

When I protect the sheet (user to select only unlocked cells) I get a runtime 1004 error.

Any ideas on how to keep this runtime error from happening?

Thanks,
Keith
---------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 11 And Target.Column = 5 Then
Worksheets("SurveyResults").Range("C5").Calculate
Worksheets("SurveyResults").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("SurveyResults").Range("C5:C6"), _
CopyToRange:=Range("E15:F15"), Unique:=False
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$E$11") = "" Then Range("$E$11") = "[Make a Selection]"
Range("$J$7") = ActiveCell.Offset(0, -1)
Range("$J$8") = ActiveCell

End Sub

Hi Keith

Create a Sub for Protecting the sheet and one for Unprotecting

Call Unprotect at the beginning of your code, then call Protect before your End sub.

--
Regards

Roger Govier


"Keith" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
I am using the subs below to run an advanced filter filtering data from another sheet.

When I protect the sheet (user to select only unlocked cells) I get a runtime 1004 error.

Any ideas on how to keep this runtime error from happening?

Thanks,
Keith
---------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 11 And Target.Column = 5 Then
Worksheets("SurveyResults").Range("C5").Calculate
Worksheets("SurveyResults").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("SurveyResults").Range("C5:C6"), _
CopyToRange:=Range("E15:F15"), Unique:=False
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$E$11") = "" Then Range("$E$11") = "[Make a Selection]"
Range("$J$7") = ActiveCell.Offset(0, -1)
Range("$J$8") = ActiveCell

End Sub

Roger,
Thanks.
Keith


"Roger Govier" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
Hi Keith

Create a Sub for Protecting the sheet and one for Unprotecting

Call Unprotect at the beginning of your code, then call Protect before your End sub.

--
Regards

Roger Govier


"Keith" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
I am using the subs below to run an advanced filter filtering data from another sheet.

When I protect the sheet (user to select only unlocked cells) I get a runtime 1004 error.

Any ideas on how to keep this runtime error from happening?

Thanks,
Keith
---------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 11 And Target.Column = 5 Then
Worksheets("SurveyResults").Range("C5").Calculate
Worksheets("SurveyResults").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("SurveyResults").Range("C5:C6"), _
CopyToRange:=Range("E15:F15"), Unique:=False
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$E$11") = "" Then Range("$E$11") = "[Make a Selection]"
Range("$J$7") = ActiveCell.Offset(0, -1)
Range("$J$8") = ActiveCell

End Sub