Excel table protection with formula copying

[Allie_Excel]

Hello wonderful people

I've been assigned the task of creating a new payroll template in Excel. However, our users have limited Excel skills, and I'm concerned that they might accidentally delete the important formulas in the template. Actually not concerned, I know they will. I've searched for solutions in various YouTube tutorials but haven't found anything that make sense so I am hoping that there is a genius somewhere in the world tonight that says “Oh my gosh – this is so easy!!!!”

I've designed the template using an Excel table, which allows formulas to automatically appear when a new row is inserted. However, I also need to lock certain columns (M, P, S, V, AH, AL, and AM), which contain the total figures. The problem is that when I lock these columns and insert a new row, the formulas no longer carry over. An error message appears. “The chart you are trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.” I definitely can’t give them the password as it defeats the purpose of locking the columns.

I've tried to attached one of the 16 templates I'm working on but it won't work and I am not familiar with XL2BB so I will start with a picture. I would greatly appreciate any suggestions or solutions to overcome this issue. According to a chat GPT (my new best friend!), it's possible to achieve this, but I've encountered difficulties when trying to follow the steps provided.

Remember - it's in a excel table format and when all columns are unlocked I can insert a row perfectly the formulars appear. As soon as I lock one column, I can still insert the row but the formulas don't appear.

Please please can someone help.

Regards from want to be better at excel Allie

 

[mee siam]

I think the only way to add a row in such a circumstance is through using macros. By looking online, I could only found this macro (do remember to change or add in the columns that are locked). If you are a bit more well versed in vba, you can even force the formula to be inside the macro. If anyone else have a better solution (like binding the commandbars to macros), I hope they can add into this too.

Sub InsertARow()
   Dim r As Long
   On Error GoTo ErrHandler
   ' Get the row number
   r = !! add in row number here etc.
   ' Unprotect the sheet
   ActiveSheet.Unprotect Password:="secret"
   ' Insert a row
   Range("C" & r).EntireRow.Insert
   ' Fill down the formula from above the inserted row to below it
   Range("C" & (r - 1) & ":C" & (r + 1)).FillDown

ExitHandler:
   ' Protect the sheet again
   ActiveSheet.Protect Password:="secret"
   ' Get out
   Exit Sub

ErrHandler:
   ' Report the error to the user
   MsgBox Err.Description, vbExclamation
   ' Always go past the exit handler section
   Resume ExitHandler
End Sub

 

[Allie_Excel]

Hello Mee Siam - just wanted to say a big thank you for taking the time out to write this for me, a stranger from the land down under! : ) You are wonderful. Honestly speaking, I am not sure how to add a VBA but there are tutorials online so I start watching them now to get my head around this. Thanks again - I am very grateful. Allie x