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

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      
    Me.Unprotect "123"
    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
    Me.Protect "123"

End Sub

 

[Norie]

Try using Me, which is a reference to the worksheet the code module 'belongs' to.

    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"

 

[DctheDC]

Thanks Fluff and Norie, but the code did not work for me, any other ideas?

 

[Fluff]

Please define "did not work"

 

[DctheDC]

It did nothing, it did not appear to unprotect the sheet and did not copy the Row, and gave no error messages

 

[Fluff]

If you put the word Stop as the first line of code & doubleclick on the sheet what happens?

 

[DctheDC]

I'm afraid that nothing is happening at all

 

[jmacleary]

Just a thought - do you have this code in the worksheet or in a module? It needs to be in the worksheet where you are expecting it to work.

 

[Fluff]

If the code is in the correct sheet module.
Run this

Sub chk()
Application.EnableEvents = True
End Sub

and then try double-clicking on the relevant sheet