SOLVED

Protect Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2365230%22%20slang%3D%22en-US%22%3EProtect%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365230%22%20slang%3D%22en-US%22%3E%3CP%3EHello..%20I%20have%20a%20workbook%20that%20has%20around%2050%20tabs%20within%20it.%20Another%20individual%20is%20responsible%20for%20updating%20approximately%205-8%20cells%2Frows%20for%2030%20of%20those%20tabs%2C%20while%20I%20am%20responsible%20for%20the%20rest%20of%20it.%20I%20have%20been%20instructed%20by%20my%20boss%20to%20protect%20the%20rest%20of%20the%20cells%2Ftabs%20to%20prevent%20changes%20to%20my%20cells%2C%20however%2C%20when%20I%20apply%20my%20macro%20for%20protecting%20all%2C%20some%20of%20my%20formatting%20is%20cleared%20out..%20specifically%20'insert%20rows'%20and%20'format%20cells'.%20I%20have%20checked%20the%20box%20for%20those%20as%20well%20as%20'select%20locked%20cells'%20(as%20was%20previously%20recommended%20as%20a%20solution)%2C%20however%2C%20it%20seems%20that%20running%20the%20macro%20unchecks%20those%20boxes.%20Does%20anyone%20have%20a%20solution%20for%20this%3F%20I%20need%20my%20macro%20as%20it%20would%20be%20inefficient%20to%20lock%20each%20one%20manually%20with%20as%20often%20as%20we%20are%20in%20and%20out%20of%20this%20workbook%2C%20but%20I%20also%20need%20that%20formatting%20to%20remain%20in%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20screenshots%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Microsoft%20Office%2016%20on%20a%20PC.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2365230%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365290%22%20slang%3D%22en-US%22%3ERe%3A%20Protect%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365290%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056843%22%20target%3D%22_blank%22%3E%40whiteen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20specify%20those%20options%20in%20the%20code.%20If%20you%20don't%2C%20they'll%20be%20disabled%20by%20default%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EwSheet.Protect%20Password%3A%3DPwd%2C%20DrawingObjects%3A%3DTrue%2C%20Contents%3A%3DTrue%2C%20Scenarios%3A%3DTrue%2C%20_%0A%20%20%20%20AllowFormattingCells%3A%3DTrue%2C%20AllowFormattingColumns%3A%3DTrue%2C%20_%0A%20%20%20%20AllowFormattingRows%3A%3DTrue%2C%20AllowInsertingRows%3A%3DTrue%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365411%22%20slang%3D%22en-US%22%3ERe%3A%20Protect%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365411%22%20slang%3D%22en-US%22%3EHans%2C%20my%20man!!!!!%20Thank%20you%20soo%20much!%20I%20previously%20tried%20this%20but%20must%20have%20had%20something%20wrong%20in%20my%20code%20because%20it%20gave%20me%20an%20error.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much!%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by allyreckerman (Microsoft)
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
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!