Unprotect Sht, Run VBA , Protect Sheet

[DctheDC]

I am using the following VBA to copy a row complete with all the Formulas, but my users keep *accidentally* deleting the formulas so I want to protect Col B:K

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      
        
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents


End Sub

I cannot figure out how to Unprotected the Sheet, run the code above to copy the Row, and then Protect the sheet again.

I have been playing with ActiveSheet.Unprotect Password:="123" and then ActiveSheet.Protect Password:="123"but can't get it right


Any ideas?

Thanks

 

[DctheDC]

jmacleary, Fluff

The Code is in a Worksheet "Sheet5(On Hire 01-01-2109)" and not a Module, does that make a difference?

 

[Fluff]

If that is the sheet you want it to work on, then that's fine.
Have you tried what I suggested in post#10?

 

[DctheDC]

I tried the post #10 idea but it did not work, unless I am doing something stupid. What I have is the following code in my Sheet5(On Hire 01-01-2019)

Sub chk()
Application.EnableEvents = True
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      
     Cancel = True
    Me.Unprotect Password:="123"
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    Me.Protect Password:="123"


End Sub

 

[Norie]

Is the code being executed?

You can check that by setting a breakpoint somewhere in the code using F9, then returning to the sheet and double clicking in a cell.

If the code is being triggered you should see a Debug dialog.

 

[Fluff]

I tried the post #10 idea but it did not work, unless I am doing something stupid. What I have is the following code in my Sheet5(On Hire 01-01-2019)

You need to run the sub chk manually, it's not automatic

 

[DctheDC]

I have just noticed that if the Sheet is Unprotected to start with then everything works OK. It inserts a Row with all the Formulas and then Protects the sheet.
If the Sheet is Protected to start with then nothing happens at all

 

[Fluff]

That code should either work, or give you an error if the password is wrong.

 

[DctheDC]

I am not getting any error messages. I just protected the sheet using the password 'QWE' and left the password in the code as '123' and it did not give me any errors either

 

[Fluff]

Is the code EXACTLY as shown in post#13?
Also are you double-clicking on sheet "On Hire 01-01-2109"?

 

[DctheDC]

Exactly as post #13 . I just copied/paste it again from the post, but nothing is happening if the sheet is Protected to begin with.
And yes, double-clicking on sheet "On Hire 01-01-2019"?