VBA Code to Protect Excel Sheet

Complete Excel VBA Course

Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? A common way to achieve this is using Unprotect function available in VBA before making changes to the sheet and then protect it back using Protect function. See below an example:

VBA Code to Protect Excel SheetExample:

Sheet1.Unprotect "123"
Sheet1.Range("A1").Value = "ABC"
Sheet1.Protect "123"

While this code works well but has small limitation, the code will produce error when Excel file is in Shared Mode. This is because you are not allowed to protect or unprotect a sheet when shared mode is on.

Here is a simple code which can help you here:

Complete Excel VBA Course

VBA Code:

Sheet1.Protect Password:="123", UserInterfaceOnly:=True

In the code you will notice that UserInterfaceOnly has been passed as True that does the trick. If you protect a sheet with UserInterfaceOnly as True then Excel allows VBA to write data on the sheet without unprotecting it.

Please note that this code needs to be executed once before you share the file.

VBA Code to protect Excel sheet

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Excel VBA Course : Beginners to Advanced

We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

This course is going to help you to excel your skills in Excel VBA with our real time case studies.

Lets get connected and start learning now. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Custom Calendar Control for MS Access​

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *