Changing default elements when locking a spreadsheet

[kndusek]

Hello! At my job, we used to manually protect spreadsheets to prevent cells with calculations from being changed. When manually protecting, we would check all of the elements in the Protect Sheet box to allow users of the worksheet to do all of those things with the spreadsheet protected; basically they could do everything except change the formulas within the locked cells. With this being forgotten more than once, I found a way to enter some VBA Coding into the spreadsheet to auto-lock upon save and close. Is there a way to either edit my coding or edit the protection settings for Excel so that these elements are automatically checked when the spreadsheet is automatically protected upon saving and closing? Thanks!

[#2]

@kndusek 

The worksheet.protect method has a whole raft of properties it is possible to set from VBA

worksheet.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect