Macro and Protected Sheet Problems

[Nitsalet]

I have a spreadsheet that suppliers need to complete. In order to standardize the data entered as much as possible, it has several drop down lists and formulas. I need to lock the cells with formulas to prevent them from deleting the content – see cells with blue text on the image. B15-22+ needs to always display a dash. I achieved this by inserting a dash in B15 and using =$B$15 in the cells below B15.
There are also a couple of macros buttons that allow them to insert rows if needed. The macro is written to insert a row and copy the formatting of the row above it. This works fine when the spreadsheet is unprotected, but as soon as I add protection I get a Run-time error as shown in the image. If I click on End, it inserts the row but does not include the formatting. The macro is copied below:

Users also need to be able to delete rows in two areas (row 15-22+ and 30-36+) which they cannot do when I add protection even though Delete rows is checked under Protect Sheet options. I feel as though these are simple fixes that I am just not experienced enough to know how to do. Any help would be appreciated.

 

[TheRedCardinal]

I think I understand the first bit of your problem. In my experience VBA cannot edit sheets that you have protected from Excel itself.

Instead you can activate protection from within VBA using a code like this:

Worksheet.Protect "Password", UserInterfaceOnly := True

You haven't defined any worksheets in your code, so make sure you properly reference it in the code, and choose your own password.

 

[Nitsalet]

Thanks for your suggestion, I will see how that goes.