Protect Sheet

[whiteen]

Hello.. I have a workbook that has around 50 tabs within it. Another individual is responsible for updating approximately 5-8 cells/rows for 30 of those tabs, while I am responsible for the rest of it. I have been instructed by my boss to protect the rest of the cells/tabs to prevent changes to my cells, however, when I apply my macro for protecting all, some of my formatting is cleared out.. specifically 'insert rows' and 'format cells'. I have checked the box for those as well as 'select locked cells' (as was previously recommended as a solution), however, it seems that running the macro unchecks those boxes. Does anyone have a solution for this? I need my macro as it would be inefficient to lock each one manually with as often as we are in and out of this workbook, but I also need that formatting to remain in place.

I've attached screenshots for reference.

I am using Microsoft Office 16 on a PC. 

Thank you!

[#2 · Accepted solution]

@whiteen 

You can specify those options in the code. If you don't, they'll be disabled by default:

wSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingRows:=True

[#3]

Hans, my man!!!!! Thank you soo much! I previously tried this but must have had something wrong in my code because it gave me an error.

Thank you very much!