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.

 

[wdgor]

Joe - you are a genius. It now works as it should work. Thank you VERY much.

When I set up the worksheet protection, I checked the boxes represented below. The user needs to be able to use Auto Filter and be able to Delete a row if they made a mistake, i.e. if one of the children should not have been created, I want them to have the ability to delete that row. When the VBA code runs, it seems to change the properties of the Protect Sheet checkboxes in that it removes the Delete and Auto Filter checkboxes that were originally there when I set up the protection options.

Does: "Worksheets("Master Worksheet").Protect" automatically set it back to the protection default of only allowing the "Select locked cells" and "Select unlocked cells" to be used? Thanks.

 

[Joe4]

The protection command would look like:

Worksheets("Master Worksheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowDeletingRows:=True, AllowFiltering:=True

(you can get this code by turning on the Macro Recorder and making the selection your show above).

Note however, you still need to contend with Locked Cells when deleting rows.

Protect a worksheet - Microsoft Support

To help improve your privacy in Excel, a password adds protection to worksheets to help prevent others from changing, moving, or deleting important data.

support.microsoft.com

 

[wdgor]

Thank you again. That allows the Delete and Auto Filter boxes to remain checked. When I use the Auto Filter on the protected columns, it works. When I try to use the Delete to delete a row, it tells me I need to Unprotect the sheet. I thought the "AllowDeletingRows:=True" would allow the user to delete a row in even in protected mode. Not true?

 

[Joe4]

Thank you again. That allows the Delete and Auto Filter boxes to remain checked. When I use the Auto Filter on the protected columns, it works. When I try to use the Delete to delete a row, it tells me I need to Unprotect the sheet. I thought the "AllowDeletingRows:=True" would allow the user to delete a row in even in protected mode. Not true?

I believe it may depend on if the cells are locked or not. If you read the link I provided, it discusses about using Locked/Unlocked cells with protection, but I have never really tried it.
And unlocking the cells may have other unintended consequences for you.

I think a better option may be to create your own procedure to delete rows that unprotects the sheet, deletes the desired rows, then re-protects the sheet again.
And then you can attach that to a button or a keyboard shortcut that users have to use if they wish to delete rows.

It seems a little odd to me to protect the sheet, but want to allow them to delete rows. Doesn't that really defeat the point of protection?

 

[wdgor]

I understand your point on the protection but the users can create a child in error. However, it would be safer to do what you suggested with a MsgBox or a ctrl combination that would allow them do it. I'll think about it and maybe not allow any deletion. The worst that could happen is to have a orphan line and I can put in some logic on the analyses I am doing from the sheet to ignore orphan records.

I want to thank you again for all your help. I have learned a lot and now have a better sheet for the users. Have a great day!!

 

[Joe4]

You are welcome.

In case you are interested, here is a little macro you can use to delete a row that you specify:

Sub MyDeleteRow()

    Dim myRow As Long
    
'   Prompt user for which row to delete
    On Error GoTo err_chk
    myRow = InputBox("Which row number would you like to delete?")
    On Error GoTo 0
    
'   Make sure they are deleting a header row (1-3)
    If myRow < 4 Then
        MsgBox "You cannot delete the header rows!", vbOKOnly, "ENTRY ERROR!"
        Exit Sub
    Else
'       Delete row
        ActiveSheet.Unprotect
        Rows(myRow).Delete
        ActiveSheet.Protect
    End If

    Exit Sub
    
err_chk:
    MsgBox "You have not entered a valid row number!", vbOKOnly, "TRY AGAIN!"
    
End Sub

 

[wdgor]

That's cool. I will try it.