Running VBA code in a protected worksheet

[wdgor]

I have VBA code that produces the output below. When a user left mouse double clicks on a row in column A the code creates a copy of the line, changes the number to X+.1 and changes the color of the line. It also changes the Task Number to concatenate the Issue number with the Client. This all works perfectly until I try to protect the columns in yellow. When I use the Me.Protect code (see below), I get an error on the Target.Offset line. I am not a VBA expert and don't know how to correct the code to allow the code to run in a protected worksheet without getting an error (see below - Run-time error '1004' - Insert method of Range class failed). I don't understand why the code works perfectly in an unprotected sheet but fails with protection. Thanks in advance for any assistance.

 

[Joe4]

I am confused about the issue. If you copy the code I updated for you above, and double-click on cells A7 or A9, I do not get any errors.
So I am having a hard time seeing what the issue is.

 

[wdgor]

I copied your code to another clean version of the same worksheet and I get the same error.

 

[Joe4]

I am unable to recreate the issue you are having.
If you can make a new workbook that has this issue, can you upload it to a file sharing site for us to see and test to see what is going on?

 

[wdgor]

I don't have a file sharing site. I don't know if you would allow it but I can email you a copy.

 

[Joe4]

I don't have a file sharing site. I don't know if you would allow it but I can email you a copy.

There are plenty of free ones out there, like DropBox.com.

 

[wdgor]

OK - I will set one up.

 

[wdgor]

Services Master List 2022_Example.xlsm

Shared with Dropbox

www.dropbox.com

I trust I did it correctly. Let me know if you have an issue getting the sheet. Again, thanks for all your help.

 

[Joe4]

OK. I will download it in a little while and take a look at it when I am at my home computer (I cannot do it from my work computer - security policy).

 

[wdgor]

Thank you sir.

 

[Joe4]

OK, I see what is happening now. In the middle of your "BeforeDoubleClick" code, it is calling your "Worksheet_Change" code (since you are updating the value of a cell), and that is re-protecting the sheet before all the changes in your "BeforeDoubleClick" code is complete. So we need to add in another line to unprotect your sheet again, i.e.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rngcurrent As Range

If Target.Column <> 1 Then
    Cancel = False
    Exit Sub

End If

If Target.Cells = "" Then
    Cancel = False
    Exit Sub
End If

    Worksheets("Master Worksheet").Unprotect
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Target.Value + 0.1
    Set rngcurrent = ActiveCell
    Worksheets("Master Worksheet").Unprotect
    Range("A" & rngcurrent.Row & ":F" & rngcurrent.Row).Interior.ColorIndex = 37
    Cancel = False
    Worksheets("Master Worksheet").Protect
    
End Sub