Advanced Filter in Protected Sheet

K

Keith

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
 
R

Roger Govier

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


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
 
K

Keith

Roger,
Thanks.
Keith


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


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
 

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

Similar Threads

Unprotect Sheet for the duration of a script 7
Advanced filter with cut data instead of copy? 1
Advanced Filter by Macro does not work anymore under Excel 2007 - Help ! 3
Search for LIKE 4
Advanced Filter Error - I cannot spot my Error 3
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Advanced Filter VBA Help 5
advanced filter question 2

Top