Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Office Scripts – Workbook & worksheet protection

As Excel users, we often find ourselves protecting and unprotecting worksheets and workbooks.  While Excel’s protection is not perfect, it does prevent others or even us from accidentally making changes.  It can be as simple as one keystroke that turns our worksheet perfection into an error-filled disaster.

Unfortunately, protecting and unprotecting is an enormous time suck.  Each of the sheets has to be protected one at a time, plus we have to type in the password correctly… twice.   If we are only protecting a single worksheet, that’s OK.  But if we are protecting more than that, we need to turn to a faster solution.

What’s even worse for the Excel online user is that the protection settings are not yet available in the ribbon (this is correct at the time of writing, but hopefully will change soon).  Which means we have to flip back and forth between desktop Excel and online Excel.  The good news is that since Office Scripts are available in Excel online, we can apply protection to lots of sheets with just a few clicks.

If you have written VBA Macros before, then much of what you know will apply directly to Office Scripts, but with a slightly different syntax.  The good news is that this post can help you transition to Office Scripts even faster.

It is not essential to read the entire post from start to finish, but doing so will help you understand more about Office Scripts.


Using this post

This post is intended to be a reference guide to help you build your own Office Scripts.  By using and adapting the code, you should be able to achieve most of what you need for protecting and unprotecting without needing to look elsewhere.

Most of the examples have been structured in a standard way.  This consistency should help with adapting the code for your own needs.  The worksheet examples are either applied to a worksheet called “Sheet1”, or to all the items within the worksheets collection.  There are many ways to refer to worksheets, so check out my Office Scripts guide to worksheets for further information for adapting this part of the code.

Some of the examples do not provide visual output.  In these circumstances, the console log is used to output the result.  You would be unlikely to use this approach in the real world, but it is useful to demonstrate the concepts.


getProtection method

Before we get into the examples, we must first look at where the worksheet, range, and workbook protection settings exist within Excel’s object model.

You will notice that every example which protects or unprotects the worksheet, range, or workbook uses the getProtection as an intermediary method.  For worksheets, getProtection is always used between the worksheet object and the protect or unprotect method (see the bold text  below).

let ws = workbook.getWorksheet("Sheet1"); 
ws.getProtection().protect();
let wsArr = workbook.getWorksheets();
for (let i = 0; i < wsArr.length ; i++) {
  wsArr[i].getProtection().protect();
  }
About getProtection (Worksheet)
Purpose: Returns the sheet protection object for a worksheet.
Syntax: ExcelScript.Worksheet.getProtection();
Parameters: None
Returns: ExcelScript.WorksheetProtection

The same is also true of the workbook, where getProtection is used between the workbook object and the protect or unprotect action (see the bold text below).

workbook.getProtection().protect();
About getProtection (Workbook)
Purpose: Returns the protection object for a workbook.
Syntax: ExcelScript.Workbook.getProtection();
Parameters: None
Returns: ExcelScript.WorkbookProtection

Finally, the same can be said when locking cells and hiding formulas.  getProtection is used between the cell format and the action (see the bold text below).

ws.getRange("A1").getFormat().getProtection().setLocked(false);
About getProtection (Range Formats)
Purpose: Returns the format protection object for a range.
Syntax: ExcelScript.RangeFormat.getProtection();
Parameters: None
Returns: ExcelScript.FormatProtection

While we don’t really use getProtection in its own right, it is a key part of the object model that we must include.


Worksheet protection

In this section, we are covering a few key actions:

  • Checking if a worksheet is protected
  • Checking which worksheet protection options have been applied
  • Protecting a worksheet – with and without passwords, and applying to multiple worksheets at once
  • Unprotecting a worksheet – with and without passwords, and applying to multiple worksheets at once

Check if a worksheet is protected

To confirm if a worksheet is already protected, we use the getProtected method.  It returns true if it is protected or false if it is not.

The example code below uses an if statement to check whether getProtected returns true.  If it is, the text “Worksheet is protected” appears in the console log; otherwise, the message “Worksheet not protected” will be displayed.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");
 
//Check if worksheet is protected
if (ws.getProtection().getProtected() == true) {

    //Action if protected    
    console.log("Worksheet is protected");

  } else {

    //Action if not protected
    console.log("Worksheet not protected");

  };

This is an important check because if a worksheet is already protected, trying to protect it again will result in an error.  Running this check helps to avoid this error.

About getProtected (Worksheet)
Purpose: Specifies if the worksheet is protected.
Syntax: ExcelScript.WorksheetProtection.getProtected();
Parameters: None
Returns: boolean (true/false)

Which protection options have been applied?

Having established whether a worksheet is protected or not.  We may also need to understand what options have been applied. For the worksheet object, there are 14 protection options.  These options coincide with those available when protecting manually in Excel desktop.

The code below displays the status of each protection option within the console log.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Display the status of each protection option in the console log
console.log(ws.getProtection().getOptions().allowAutoFilter);
console.log(ws.getProtection().getOptions().allowDeleteColumns);
console.log(ws.getProtection().getOptions().allowDeleteRows);
console.log(ws.getProtection().getOptions().allowEditObjects);
console.log(ws.getProtection().getOptions().allowEditScenarios);
console.log(ws.getProtection().getOptions().allowFormatCells);
console.log(ws.getProtection().getOptions().allowFormatColumns);
console.log(ws.getProtection().getOptions().allowFormatRows);
console.log(ws.getProtection().getOptions().allowInsertColumns);
console.log(ws.getProtection().getOptions().allowInsertHyperlinks);
console.log(ws.getProtection().getOptions().allowInsertRows);
console.log(ws.getProtection().getOptions().allowPivotTables);
console.log(ws.getProtection().getOptions().allowSort);
console.log(ws.getProtection().getOptions().selectionMode);

We won’t go through these individually as they apply the same settings as the standard Excel desktop interface.  But it is worth noting that all the options have a true or false value, except for selectionMode, which has none, normal or unlocked values.

About getOptions
Purpose: Returns the protection options for the worksheet.
Syntax: ExcelScript.WorksheetProtection.getOptions();
Parameters: None
Returns: ExcelScript.WorksheetProtectionOptions

Protect a worksheet

To protect a worksheet, we used the protect method.  This gives us the ability to apply any of the options noted in the section above, along with defining a password.

One of the key points to note about the protect method is that it cannot be applied to an already protected worksheet.  Doing so will trigger an error: WorksheetProtection protect: This operation is not permitted for the current object.

Therefore, before protecting a sheet, we should either:

  • check it is unprotected
  • apply the unprotect method before protecting.
About protect (Worksheet)
Purpose: Protects a worksheet. Fails if the worksheet has already been protected.
Syntax: ExcelScript.WorksheetProtection.protect(options?, password?);
Parameters:

  • options: ExcelScript.WorksheetProtectionOptions – Optional sheet protection settings.
  • password: string – optional parameter to set a password

Returns: void

Protect a worksheet without a password

The following example demonstrates how to use the protect method without any parameters.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Protect worksheet without password
ws.getProtection().protect();

Protect a worksheet with a password

The following code builds on the example above by adding a password.  The password parameter is the second parameter within the method; therefore, we have used two curly brackets ( { } ) in place of the first parameter.  The use of the two curly brackets avoids the need to apply the protection options.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Protect worksheet with a password
ws.getProtection().protect({},"myPassword");

Protect all worksheets  – Method #1 forEach loop

Applying protection one sheet at a time can be time-consuming.  The following code uses a forEach loop to apply protection to every item in the worksheet collection.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Protect all worksheets in workbook - Method #1
wsArr.forEach(ws => {
  ws.getProtection().protect();
  });

Protect all worksheets  – Method #2 for loop

An alternative method to the forEach loop is the for loop, as shown by the example below.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Protect all worksheets in workbook - Method #2
for (let i = 0; i < wsArr.length ; i++) {
  wsArr[i].getProtection().protect();
  };

As the for loop consists of three parts, which are separated by semi-colons, we have a lot of control over the loop

  • i = 0 : The first parameter is the initializer.  i is used as a number variable.
  • i < wsArr.length : The loop is executed while this condition continues to be true.
  • i++ : The final argument increments the i variable at specific intervals. i++ adds 1 to i for each iteration.

Because we can control each of the three parts, the for loop gives us a lot of flexibility as we do not need to loop through every item in the collection.

Applying protection options

We saw the protection options above when we looked at the getOptions method.  Those options are applied using the protect method.  This is demonstrated in the code below.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set individual options when protecting
ws.getProtection().protect({
  allowAutoFilter: false,
  allowDeleteRows: false,
  allowDeleteColumns: false,
  allowEditObjects: false,
  allowEditScenarios: false,
  allowFormatCells: false,
  allowFormatColumns: false,
  allowFormatRows: false,
  allowInsertColumns: false,
  allowInsertHyperlinks: false,
  allowInsertRows: false,
  allowPivotTables: false,
  allowSort: false,
  selectionMode: ExcelScript.ProtectionSelectionMode.none
  });

The last option in the code above, ExcelScript.ProtectionSelectionMode, can have three possible values (none, normal and unlocked).  The code for all 3 options are shown below.

selectionMode: ExcelScript.ProtectionSelectionMode.none
selectionMode: ExcelScript.ProtectionSelectionMode.normal
selectionMode: ExcelScript.ProtectionSelectionMode.unlocked
  • none: No cells can be selected
  • normal: All cells can be selected
  • unlocked: Only unlocked cells can be selected

Unprotect a worksheet

To unprotect a worksheet, we use the unprotect method.  If a password was used to protect the sheet, a password is also required to unprotect it.

If the wrong password is used, it will result in an error which states: WorksheetProtection unprotect: The argument is invalid or missing or has an incorrect format.

About unprotect (Worksheet)
Purpose: Unprotects a worksheet.
Syntax: ExcelScript.WorksheetProtection.unprotect(password?);
Parameters:  password: string – optional parameter for the sheet protection password
Returns: void

Unprotect a worksheet without a password

The following example demonstrates how to use the unprotect method without a password.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");
//Unprotect worksheet without password
ws.getProtection().unprotect();

Unprotect a worksheet with a password

The code below builds on the example above by adding a password to unprotect the worksheet.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Unprotect worksheet with password
ws.getProtection().unprotect("myPassword");

Catching errors when incorrect password entered

As noted above, providing an incorrect password results in an error within the console log.  It is much better to catch the error to manage the user journey.

In the example below, an incorrect password has been used.  The error is caught, and then a message is displayed in the console log.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Try unprotecting the worksheet with a password
try {

  ws.getProtection().unprotect("wrongPassword")
  
  //Catch the error for an incorrect password
  } catch (err) {

    //Display a message in the console log
    console.log ("Incorrect password"); 
  };

Unprotect all worksheets  – Method #1 forEach loop

The following code uses a forEach loop to unprotect every item in the worksheet collection.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Unprotect all worksheets in workbook - Method #1
wsArr.forEach(ws => {
  ws.getProtection().unprotect();
});

Unprotect all worksheets  – Method #2 for loop

Another option to unprotect all worksheets is to use the for loop, as demonstrated below.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Unprotect all worksheets in workbook - Method #2
for (let i = 0; i < wsArr.length ; i++) {
  wsArr[i].getProtection().unprotect();
 };
100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.


Cell locked settings

When a sheet is protected with the ProtectionSelectionMode set to normal or unlocked, some cells can still be edited.  To obtain the current setting for a specific range, we use the getLocked method, and to change the setting, we use the setLocked method.

Return the lock setting

The getLocked method provides a true or false result to indicate whether a range has the lock setting applied.  The setting is part of a range’s format.

The code below checks the lock setting of cell A1 and returns true or false into the console log accordingly.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Check a ranges lock setting
console.log(ws.getRange("A1").getFormat().getProtection().getLocked());

When the worksheet is unprotected, the lock setting has no impact; it only comes into force when protection is applied.

About getLocked
Purpose: Confirms if Excel locks the cells in the object. A null value indicates that the entire range doesn’t have a uniform lock setting.
Syntax: ExcelScript.FormatProtection.getLocked();
Parameters: None
Returns: boolean (true/false)

Change the lock setting

To change the lock setting, we use the setLocked method.

The example below uses setLocked to change cell A1 to be locked.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the lock setting of a range
ws.getRange("A1").getFormat().getProtection().setLocked(true);

If we want to change the lock settings for an entire worksheet, we can achieve that by not providing any range address into the getRange method.  For example, the code below sets the entire worksheet to not be locked.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the lock setting for entire range of worksheet
ws.getRange().getFormat().getProtection().setLocked(false);
About setLocked
Purpose: Specifies if Excel locks the cells in the object.
Syntax: ExcelScript.FormatProtection.setLocked(locked);
Parameters: locked: boolean (true/false)
Returns: void

Hiding formulas

Personally, I don’t consider hiding a range’s calculation to be part of its protection.  However, Excel does; therefore, I have provided this section for completeness.

If a range is formatted with hidden, the calculation of that range cannot be seen when protection is applied to the worksheet.  To obtain the current setting, we use the getFormulaHidden method, and to change the setting, we use the setFormulaHidden method.

Return the formula hidden setting

The getFormulaHidden method provides a true or false result to indicate if a range has the hidden setting applied.  The setting is part of a range’s format.

The code below checks the hidden setting of cell A1 and returns true or false into the console log accordingly.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Check formula hidden setting for a range
console.log(ws.getRange("A1").getFormat().getProtection().getFormulaHidden());

When the worksheet is unprotected, the formula hidden setting has no impact; it only comes into force when worksheet protection is applied.

About getFormulaHidden
Purpose: Confirms if Excel hides the formula for the cells in the range. A null value indicates that the entire range doesn’t have a uniform formula hidden setting.
Syntax: ExcelScript.FormatProtection.getFormulaHidden();
Parameters: None
Returns: boolean (true/false)

Change the formula hidden setting

To change the formula hidden setting, we use the setFormulaHidden method.

The example below uses setFormulaHidden to change cell A1 to have formulas hidden.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the formula hidden setting of a range
ws.getRange("A1").getFormat().getProtection().setFormulaHidden(true);

If we want to change the hidden settings for an entire worksheet, we can achieve that by not providing any range address into the getRange method.  For example, the code below sets the entire worksheet to have the hidden setting.

//Assign the Sheet1 worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the formula hidden setting for entire worksheet
ws.getRange().getFormat().getProtection().setFormulaHidden(true);
About setFormulaHidden
Purpose: Specifies if Excel hides the formula for the cells in the range.
Syntax: ExcelScript.FormatProtection.setFormulaHidden(formulaHidden);
Parameters: formulaHidden: boolean (true/false)
Returns: void

Workbook protection

In this section, we are covering a few key actions:

  1. Check if a workbook is protected
  2. Protecting a workbook – with and without passwords
  3. Unprotecting a workbook – with and without passwords

In Excel online, the top-level object is the workbook.  The workbook variable is declared as part of the main function.  Therefore, in the examples, I have not re-declared a workbook variable.  But if the code is used outside of the main function then the workbook variable may need to be re-declared.

Check if a workbook is protected

We use the getProtected method to confirm if a workbook already has protection applied.  A true result means it is protected; a false result means it is not.  An error occurs from trying to protect an already protected workbook.

The example code below uses an if statement to check whether getProtected returns true.  If it is true, the text “Workbook is protected” appears in the console log; otherwise, the message “Workbook not protected” will be displayed.

//Check if workbook is protected

  if (workbook.getProtection().getProtected() == true) {

    //Action if protected
    console.log("Workbook is protected");

  } else {

    //Action if not protected
    console.log("Workbook not protected");

  };
About getProtected (Workbook)
Purpose: Specifies if the workbook is protected.
Syntax: ExcelScript.WorkbookProtection.getProtected();
Parameters: None
Returns: boolean (true/false)

Protect a workbook

Due to the lack of additional options, protecting workbooks is similar, but easier than it is worksheets.

Just like for worksheets, protection cannot be applied to a workbook that is already protected.  Doing so will trigger an error:  WorkbookProtection protect: This operation is not permitted for the current object.

Therefore, before applying protection to a workbook, we should either:

  • check the workbook is unprotected
  • apply the unprotect method before protecting
About protect (Workbook)
Purpose: Protects a workbook. Fails if the workbook has already been protected.
Syntax: ExcelScript.WorkbookProtection.protect(password?);
Parameters:  password: string – optional parameter to set a password
Returns: void

Protect a workbook without a password

The following example shows how to protect a workbook without a password.

//Protect workbook without password
workbook.getProtection().protect();

Protect a workbook with a password

The code below builds on the above by adding a password to the protect method.

//Protect workbook with password
workbook.getProtection().protect("myPassword");

Unprotect a workbook

To unprotect a workbook, we use the unprotect method.  If a password was used to protect, then a password is also required to unprotect.

If the wrong password is used, it returns an error into the console log: WorkbookProtection unprotect: The argument is invalid or missing or has an incorrect format. 

About unprotect (Workbook)
Purpose: Unprotects a workbook.
Syntax: ExcelScript.WorkbookProtection.unprotect(password?);
Parameters: password: string – optional parameter for the workbook protection password
Returns: void

Unprotect a workbook without a password

The following example uses the unprotect method without the password parameter.

//Unprotect workbook without a password
workbook.getProtection().unprotect();

Unprotect a workbook with a password

The code below demonstrates how to unprotect a workbook with a password.

//Unprotect workbook with password
workbook.getProtection().unprotect("myPassword");

Catching errors when incorrect password entered

As noted above, providing an incorrect password results in an error.  Therefore, it is preferable to catch the error and control what happens next.

In the example below,  an incorrect password is supplied.  The error is caught, and the text “Incorrect password” is displayed in the console.log.

//Catch error from using incorrect workbook password

try {

    workbook.getProtection().unprotect("wrongPassword")

  } catch (err) {

    //Action if incorrect password
    console.log ("Incorrect password");

  };


Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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