Macro won't work when Protect Sheet is on

[smichael]

I have a macro I have been using to search throughout data, but it doesn't seem to work with a protected sheet.

"Run-time error '1004':
You cannot use this command on a protected sheet. To use this....." pops up when trying to use.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:G1752").AutoFilter Field:=8, Criteria1:="TRUE"
    End If
End If
End Sub

Is there any way to do this with a protected sheet?
Thanks!

 

[VoG]

Try like this

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect password:="pw"
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:G1752").AutoFilter Field:=8, Criteria1:="TRUE"
    End If
End If
Me.Protect password:="pw"
End Sub

change pw to the actual password.

 

[smichael]

Thanks for the quick reply! This does work, but is it possible to do this while others cannot edit the data in the range (A7:G1752) ?

 

[VoG]

It re-protects the sheet at the end. Users won't be able to edit anything whilst the code is running.

 

[smichael]

When I applied that to the code, the search worked fine but I was still able to click and edit the data in the range (and only in that section). Do you have any suggestions on what to do? Thanks again

 

[VoG]

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect password:="pw"
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:G1752").AutoFilter Field:=8, Criteria1:="TRUE"
    End If
End If
Range("A7:G1752").Locked = True
Me.Protect password:="pw"
End Sub

 

[smichael]

Works perfectly! Thanks for all the help

 

[smichael]

After Sharing the Sheet, an error pops up whenever a cell is edited saying:

"Run-time error '1004':
Method of 'Unprotect' of object '_Worksheet' failed"

is this able to be corrected?

 

[VoG]

Not with a shared workbook as far as I know.