Excel Work Book Global Password Protection On/Off

EE Pros,

I have a very large WB with about 8 WSs that have a system of turning on and off Protection to help guide a user of the WB.  I have also built a Admin. Tab that allows for someone to do some administrative modifications to the WS's if they like.  Here is my question;

Do you have or can we create a short macro, that when fired will disable Protection throughout the WB?  Then when fired again, will turn protection back on and adhere to the countless references of ProtectOn or ProtectOFF that I have in my WSs throughout my WB?  This way, I never really have to give out a password and at the same time, the Admin. or person wanting to modify a WS, can easily disable Password protection at the WS level.  This should not interfere with the Developer Protection for the WB for changing macros.

That's it!  Thank you in advance,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
If you are unprotecting your worksheet only due to the fact so that you can run some code on it..Then in that case you can use something like this...

Sub prt()

    Sheets("Sheet1").Protect Password:="abc", userinterfaceonly:=True

End Sub

Open in new window

So in the code it protects sheet1 for the user..However if you run the macro code on sheet1 even after protection it will get execute and you don't have to unprotect it since userinterfaceonly enables to give command through excel macro without unprotecting the worksheet...

Bright01Author Commented:
Here's the problem,  This won't give the User or Admin. the ability to go in, turn off protection and make changes to the WB since protection will still be on.  Right?  

Do I place this in the THIS Workbook section?

So do I need to do anything with the Protect/Unprotect Macro references I already have in place?   Or do I leave them just as they are and simply add the code?

Saurabh Singh TeotiaCommented:
Yes on the front end the worksheets or workbook will still be protected..

You can place the code wherever you want to place them..And you don't need anything for this all you need to change the sheet name and you will be good...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Great job!  Sorry for the delay..... I've been swamped.

Thanks again,

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.