Home

Strong Protection for my Worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-329617%22%20slang%3D%22en-US%22%3EStrong%20Protection%20for%20my%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329617%22%20slang%3D%22en-US%22%3EI%20work%20at%20the%20Ministry%20of%20Education.%20I%20designed%20a%20broadsheet%20for%20all%20schools%20in%20my%20state.%20It%20is%20expected%20of%20each%20school%20in%20the%20entire%20state%20to%20enter%20their%20students'%20scores%20without%20any%20reason%20to%20change%20my%20formulae%20and%20the%20arrangement%20of%20the%20sheets.%20To%20ensure%20this%2C%20I%20set%20the%20sheet%20protection%20with%20password.%20But%20to%20my%20dismay%2C%20many%20schools%20had%20made%20series%20of%20attempt%20to%20break%20the%20protection.%20Some%20even%20deleted%20the%20formulae%20without%20the%20knowledge%20of%20how%20to%20return%20the%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%2C%20what%20can%20I%20do%20to%20ensure%20strong%20protection%20without%20the%20use%20of%20macros%2FVBA%3F%20Thanks%20for%20your%20usual%20readiness%20to%20assist.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-329617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329764%22%20slang%3D%22en-US%22%3ERe%3A%20Strong%20Protection%20for%20my%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329764%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20I%20was%20told%20not%20to%20use%20vba%20because%20it%20requires%20some%20settings%20when%20put%20on%20the%20individual%20PCs.%20We%20want%20something%20that%20can%20be%20easily%20used%20by%20different%20schools.%20However%2C%20I've%20learnt%20something%20helpful%20from%20both%20of%20you.%3CBR%20%2F%3EAgain%2C%20Thank%20you%20and%20best%20regards.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329761%22%20slang%3D%22en-US%22%3ERe%3A%20Strong%20Protection%20for%20my%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329761%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20Meanwhile%2C%20it%20interests%20me%20to%20learn%20from%20you%20that%20office%20365%20could%20be%20got%20free.%20Please%2C%20how%20do%20I%20go%20about%20that.%20I%20have%20also%20tried%20to%20achieve%20that%20at%20the%20Microsoft%20education%20community.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329710%22%20slang%3D%22en-US%22%3ERe%3A%20Strong%20Protection%20for%20my%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329710%22%20slang%3D%22en-US%22%3E%3CP%3EIMHO%2C%20worksheet%20protection%20is%20relatively%20easy%20to%20remove%2C%20doesn't%20matter%20how%20strong%20password%20do%20you%20use.%20Perhaps%20some%20third%20party%20utilities%20like%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdoneex.com%2Fprotect-excel-formulas%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdoneex.com%2Fprotect-excel-formulas%2F%3C%2FA%3E%20could%20help%2C%20didn't%20play%20with%20the.%20But%20that%20will%20be%20VBA%20inside.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-329667%22%20slang%3D%22en-US%22%3ERe%3A%20Strong%20Protection%20for%20my%20Worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-329667%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F99990%22%20target%3D%22_blank%22%3E%40Sotannde%20Abiodun%20Kabir%3C%2FA%3E%20!%20There%20are%20at%20least%202%20ways%20to%20realise%20a%20strong%20protection.%3C%2FP%3E%3CP%3E1.%20Go%20to%20your%20worksheet%20you%20want%20to%20protect.%3C%2FP%3E%3CP%3E2.%20Select%20the%20range%20of%20cells%2C%20which%20should%20be%20ONLY%20for%20inserting%20numbers.%3C%2FP%3E%3CP%3E3.%20Right-click%20on%20one%20of%20those%20selected%20cells.%3C%2FP%3E%3CP%3E4.%20Go%20to%20the%20last%20tab%20in%20this%20dialogue%2C%20%22Protection%22%3C%2FP%3E%3CP%3E5.%20Deactivate%20the%20cell%20protection%20for%20these%20cells.%3C%2FP%3E%3CP%3E6.%20Click%20on%20tab%20%22Review%22%20-%20click%20on%20Sheet%20protection.%20Activate%20only%20the%20second%20hook.%3C%2FP%3E%3CP%3E7.%20Give%20a%20password.%3C%2FP%3E%3CP%3E8.%20Now%20your%20password%20is%20a%20protection%20for%20all%20the%20cells%20NOT%20been%20protected.%3C%2FP%3E%3CP%3E9.%20If%20you%20click%20on%20one%20of%20your%20cells%20you%20ll%20see%20that%20your%20formulas%20are%20protected.%3C%2FP%3E%3CP%3E10.%20BUT%3A%20The%20range%20you%20didn't%20protect%20is%20yet%20ready%20to%20insert%20content%20like%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20only%201%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20in%20my%20mind%20is%20the%20following%3A%3C%2FP%3E%3CP%3E1.%20If%20you%20have%20VBA%20then%20go%20to%20your%20VBA%20environment%20in%20this%20workbook%20with%20shortcut%20ALT%20%2B%20F11.%20In%20your%20left%20navigation%20(Project%20Explorer%20of%20Modules%20and%20objects)%20reduce%20your%20objects%20views%20so%20that%20all%20your%20modules%20and%20objects%20are%20not%20shown%20in%20the%20second%20area%20(the%20code%20procedures).%3C%2FP%3E%3CP%3E2.%20Go%20to%20the%20last%20Tab%20%22Extras%22%20-%20click%20on%20the%20second%20last%20option%20about%20properties%20of%20your%20VBA%20Project.%3C%2FP%3E%3CP%3E3.%20Click%20on%20second%20tab%20in%20this%20dialogue.%20Disable%20the%20ability%20to%20show%20reduced%20Modules%20(Hook).%3C%2FP%3E%3CP%3E4.%20give%20a%26nbsp%3B%20password.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20screenshots%20for%20you.%20Additionally%20you%20should%20think%20about%20the%20options%20to%20migrate%20your%20Excel%20database%20(broadsheet)%20to%20SharePoint%20and%20Office%20365%20to%20share%20your%20file%20as%20an%20Excel%20Online%20workbook%20within%20OneDrive%20or%20SharePoint.%20Then%20its%20easier%20to%20figure%20out%2C%20which%20person%20has%20access%20and%20which%20changes%20will%20be%20accepted%20or%20denied%20in%20future.%20Go%20on%20with%20learning!%20and%20its%20free%20for%20eductional%20%2F%20non%20profit%20orgs%20to%20get%20an%20Office%20365%20environment.%20Unfortunately%20your%20VBA%20Code%20wont%20work%20anymore%20i%20have%20to%20admit.%20But%20in%20the%20long%20run%20your%20profit%20would%20be%20immense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreets%2C%20Eva.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sotannde Abiodun Kabir
Occasional Contributor
I work at the Ministry of Education. I designed a broadsheet for all schools in my state. It is expected of each school in the entire state to enter their students' scores without any reason to change my formulae and the arrangement of the sheets. To ensure this, I set the sheet protection with password. But to my dismay, many schools had made series of attempt to break the protection. Some even deleted the formulae without the knowledge of how to return the formula.

Please, what can I do to ensure strong protection without the use of macros/VBA? Thanks for your usual readiness to assist.
4 Replies

Hello @Sotannde Abiodun Kabir ! There are at least 2 ways to realise a strong protection.

1. Go to your worksheet you want to protect.

2. Select the range of cells, which should be ONLY for inserting numbers.

3. Right-click on one of those selected cells.

4. Go to the last tab in this dialogue, "Protection"

5. Deactivate the cell protection for these cells.

6. Click on tab "Review" - click on Sheet protection. Activate only the second hook.

7. Give a password.

8. Now your password is a protection for all the cells NOT been protected.

9. If you click on one of your cells you ll see that your formulas are protected.

10. BUT: The range you didn't protect is yet ready to insert content like numbers.

 

This is only 1 way.

 

The second in my mind is the following:

1. If you have VBA then go to your VBA environment in this workbook with shortcut ALT + F11. In your left navigation (Project Explorer of Modules and objects) reduce your objects views so that all your modules and objects are not shown in the second area (the code procedures).

2. Go to the last Tab "Extras" - click on the second last option about properties of your VBA Project.

3. Click on second tab in this dialogue. Disable the ability to show reduced Modules (Hook).

4. give a  password.

 

I attached screenshots for you. Additionally you should think about the options to migrate your Excel database (broadsheet) to SharePoint and Office 365 to share your file as an Excel Online workbook within OneDrive or SharePoint. Then its easier to figure out, which person has access and which changes will be accepted or denied in future. Go on with learning! and its free for eductional / non profit orgs to get an Office 365 environment. Unfortunately your VBA Code wont work anymore i have to admit. But in the long run your profit would be immense.

 

Greets, Eva.

IMHO, worksheet protection is relatively easy to remove, doesn't matter how strong password do you use. Perhaps some third party utilities like https://doneex.com/protect-excel-formulas/ could help, didn't play with the. But that will be VBA inside.

Highlighted
Thank you very much. Meanwhile, it interests me to learn from you that office 365 could be got free. Please, how do I go about that. I have also tried to achieve that at the Microsoft education community.
Thank you very much. I was told not to use vba because it requires some settings when put on the individual PCs. We want something that can be easily used by different schools. However, I've learnt something helpful from both of you.
Again, Thank you and best regards.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies