Insert Rows in Protected Sheet

[eforti]

Hello All,
I have a sheet I'm using to track schedules.

Columns B:C are merged and contain task names, while the other columns to the right have corresponding dates. I do not want people to be able to change the names of the existing tasks in B:C. I do however want them to be able to add rows and be able to update the new cells with task names.

I know when I protect the sheet I can allow people to add/delete rows. What I don't know how to do is force new rows to inherit the merged cell property in B:C and to have all cells in the new rows be unprotected, allowing them to add text into the new Task Names fields.

The caveat I'll throw in is that I don't want a button of any sort to add the rows. I'd like people to be able to add rows as they normally would and for the coding to be triggered from that "event".

Any ideas?

Thanks in advance!

 

[mumps]

You should be aware that merged cells cause havoc for Excel macros and should be avoided at all cost. I would suggest that you unmerge B:C. The following macro may do what you want if you unmerge the columns. Start by unlocking all the cells in the sheet. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a task name in column B and exit the cell. Once you exit the cell, it will be locked so that the names of the task cannot be changed. All other cells are left unlocked for data entry. You will still be able to insert rows.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Target.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True
End Sub

 

[eforti]

Hello mumps,
Thanks for the response and guidance! I was able to get some help on this outside of the forum as well and got the code where I needed. Sharing below for anyone else that may need the solution in the future.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = Columns.Count Then
ActiveSheet.Unprotect Password:=""
Range("B" & Target.Row).Value = "=B" & Target.Row - 1 & " + 1"
Range("B" & Target.Row + 1).Value = "=B" & Target.Row & " + 1"
Range("C" & Target.Row & ":D" & Target.Row).Merge
Target.EntireRow.Locked = False
ActiveSheet.Protect Password:="", AllowInsertingRows:=True, AllowDeletingRows:=True, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingHyperlinks:=True

End If


End Sub

 

[mumps]

Glad it worked out.