Protect & Unprotect Sheet in AutoCall Macro

Hi Friends,

any solution for my case?

im trying to creat auto call macro (simple solver - in another module) with this code :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim KeyCells As Range
Set KeyCells = Range("$D$15:$F$19")

Run "macro1"

Sheets("sheet1").Select
ActiveSheet.Unprotect Password:="love"

End Sub

Its works to autocall macro (with password : love), when im change the row from D15 : F19 with ENTER
But after ENTER i want the sheet protected again

i had try to add this code before End Sub :
Sheets("sheet1").Select
ActiveSheet.Protect Password:="love"

but still failed. Anyone help me ? :)

thank you

Welcome to the forum! Normally,

I set Protect with the UserInterfaceOnly option so all my code can change sheet objects. In ThisWorkbook object:

Code:

---

Private Sub Workbook_Open() 
  Dim ws As Worksheet
  For Each ws In Worksheets
    ' UserInterfaceOnly:=True allows code to change data.
    ws.Protect "ken", UserInterfaceOnly:=True, DrawingObjects:=True, _
      Contents:=True, Scenarios:=True
  Next ws
End Sub

---

Change ken to suit. Or, just do it for one sheet there or at the beginning of your macro. Then you don't have to worry about Unprotect/Protect each time.

Quote:

---

Originally Posted by Kenneth Hobs

Welcome to the forum! Normally, I set Protect with the UserInterfaceOnly option so all my code can change sheet objects. In ThisWorkbook object:

Code:

---

Private Sub Workbook_Open()    Dim ws As Worksheet  For Each ws In Worksheets    ' UserInterfaceOnly:=True allows code to change data.    ws.Protect "ken", UserInterfaceOnly:=True, DrawingObjects:=True, _      Contents:=True, Scenarios:=True  Next wsEnd Sub

---

Change ken to suit. Or, just do it for one sheet there or at the beginning of your macro. Then you don't have to worry about Unprotect/Protect each time.

---

Hi Ken, Thank you for your feedback.. But im still failed use you suggestion code..Could you share sample excel file for your code..?Thank you

Not much to it....