Protected Sheet - Nag error when VBA modifying cells?

[ck4242]

First time doing this...

I've created a script at this point that is doing what I want.
I've also applied this bit below to the Sheet tab in order to cause the cells to be updated automatically any time certain cells are modified:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:B17")) Is Nothing Then
Call FindOptimized_APs
End If
End Sub

That works great....except when I finally locked the sheet down and protected it.
Now, every time I modify one of the user-configurable cells, I get the message:
"Run-time error '1004':
The cell or chart you're trying to change is on a protected sheet. Blah, blah, blah.

It appears to be caused by the script setting a value to one of the cells in the sheet (which is locked).
I can band-aid this by simply unlocking that one cell...but it would be nice if I didn't have to do that.

I've found something that supposedly fixes this, but I don't know how it's applied.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Target.Locked = True Then Application.Undo

    Application.EnableEvents = True

End Sub

Is this ALSO supposed to be applied to the sheet tab? If so, can I combine both of these onto the same tab?

 

[DanteAmor]

Try this.
You must unprotect before modifying the sheet, then modify the sheet and then protect.
Change "abc" by the password of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:B17")) Is Nothing Then
activesheet.unprotect "[COLOR=#ff0000]abc[/COLOR]"
Call FindOptimized_APs
activesheet.protect "[COLOR=#ff0000]abc[/COLOR]"
End If
End Sub

 

[Joe4]

Unless the cell that you "FindOptimized_Aps" is updating is in the range B4:B17, you don't need to worry about having to use EnableEvents = False to avoid getting caught in a loop.

If you do not want to unprotect that one cell, then the way to do it is have your macro temporarily unprotect the sheet while it runs and then re-protect it when it is done.
See here: https://www.excelanytime.com/excel/...t-worksheetprotect-method&catid=79&Itemid=475
You would just put that code around your macro call.


EDIT: Too slow! DanteAmor already answered while I was posting this.

 

[ck4242]

Awesome, guys! That worked a treat.
Appreciate the quick responses!

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.