Use Macros on Password Protected Worksheets in Excel

How to make macros that will work on password protected worksheets in Excel - this includes a step-by-step guide to modifying your macros to work on these protected worksheets as well as a few additional steps that will help you build more robust macros for this scenario.

In addition to working on protected worksheets, you will learn how to use VBA / Macros to check if a worksheet is protected and also to enable a macro to work on a worksheet while keeping it password protected for the user.

After viewing this tutorial, you should have no trouble working on protected worksheets.

Enjoy!

[MsLarianne]

amazing! I've been trying to run a clear macro on a protected sheet with no success. This is so elegant and simple. Thank you

[MrtnX]

Thanks a lot. Finally a protection code that actually works.

[abdelrahmangharieb]

Thanks. This is just the information I've been looking for. Solved my issue

[Lodgeitude]

Thanks. This is just the information I've been looking for. Solved my issue.

[abdullahalshaikh]

Thanks, What I exactly needed, Spot on

[melkamzenatube]

I love so much bro, you did a lot to me

[jstan]

That's great tutorial. Can you do one with VBA Button to run with Protected sheet Please

[wizzarin]

NEED HELP - When I password protected by sheet I added "format cell", "Format columns" and "format rows" from the selection in the password "Allow all users of this worksheet to:" pick list. I did add that ActiveSheet.Protect "123" to my macro, The PROBLEM when I ran the macro, the protection diverted back to the basic "Select Locked Cells" and "Select Unlocked Cells" only and removed what I added. So after running the macro I no longer was able to format. How can I keep what I select in the Protect Sheet drop-down list? (using Excel 2019)

[wayneedmondson]

Thanks.. good tips on PW protecting and worksheet security. Thanks for the insights.. learned something new.. particularly the UserInterfaceOnly:=True argument. Thumbs up!!

[abdulrahmanbalafaringani]

I protected my diagrams from being selected or edited with password but after applying some macro on the sheet the diagrams on the sheet became selectable and editable even protected with password. It like the protection does not work anymore when I apply micro. Please help.

[anil]

Sir, how to protect workbook without opening into d:drive with all files in many folders and sub folders

[cmlosaria]

I cannot use slicers and Macros simultaneously. When I ran the macro using this method, the slicer becomes disabled again. So I need to unprotect everytime

[roaamer]

I am trying to input the code into my macro in Excel 2016 but it gives me an error:
Run-time error 1004
Application-defined or object-defined error

Could you please assist?

[kishsand]

When i used the code it says the document is not open on the line " if activesheet.protectcontents" ..

Can you please help me in this

[rajkumarkarapa]

i dont like to write password in macros. is there any solution to run macros in protected sheet