Re: Advanced Filter in Protected Sheet


"Roger Govier" <roger@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:uCq3VAqoHHA.1476@xxxxxxxxxxxxxxxxxxxxxxx
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.


Roger Govier

"Keith" <KRosewitz@xxxxxxxxxxxx> wrote in message news:135q3c18c5lbba2@xxxxxxxxxxxxxxxxxxxxx
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?


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 11 And Target.Column = 5 Then
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

Relevant Pages

  • Re: Lock Unlock cells in VBA
    ... You could unprotect the sheet, ... Note howevewr that setting the Protect method's ... Private Sub Workbook_Open ... Right-click the Excel icon on the worksheet (or the icon ...
  • Re: Asking user to continue?
    ... ' .Protect 'place at the end of the code ... All these cell can have contents removed. ... Sub ClearDATAinSUPPLIESsheet() ... SUPPLIES sheet. ...
  • RE: Excel - randomly locking cells?
    ... then protect the sheet, as I expected I get an error at the very first ... I don't like the name of your sub, and I suspect that neither does Excel. ... I right click, format cells, and uncheck Lock Cells on the protection tab. ... I protect my worksheet. ...
  • Re: Spread Sheet Tabs
    ... >> worksheet then protect the VBA code so's users could not get to it. ... >> Private Sub Workbook_BeforeClose ... >> To unhide the sheet when you want to edit. ... >>>the spread sheet and reopened as Read Only and anyone can go to Format ...
    ... To protect the text that is in the header and the footer of a Word ... Private WithEvents wdApp As Word.Application ... Private Sub Document_New ...