Protect & Unprotect Sheet in AutoCall Macro

[victor86]

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

[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:

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.

[victor86]

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

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

[Kenneth Hobs]

Not much to it....