Protection Levels in Excel Add-in using C# (VSTO) Code

Protection Levels in Excel Add-in using C# (VSTO) Code

Protection

Method to protect a file or its contents by undesired users. To Understand what level of protection a Excel file has? I am trying to implement it through code in two categories which you can reuse. To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password.

Points to be noticed

  1. Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet
  2. Protecting a worksheet is not the same as protecting an Excel file or a workbook with a password. See below for more information
  3. To lock your file so that other users can’t open it, see Protect an Excel file
  4. To prevent users from adding, modifying, moving, copying, or hiding/unhiding sheets within a workbook, see Protect a workbook

Protect

Click on Review tab and navigate to Protect group and choose desired command to apply protection as shown below:

Worksheet Protection

Following options can be applied on a worksheet to protect a worksheet or its contents. Password field require at least one character password and upon hitting OK Excel will reconfirm the password.

Various Protections

  • ProtectContents : It is used to protect contents of the sheet
  • ProtectDrawingObjects : Helps protecting Drawing Objects on a sheet like shapes
  • ProtectScenarios : Helps protecting Excel Scenarios
  • ProtectStructure : Helps protecting Row/Columns or working area in Excel Sheet. with the help of this option you can stop user to add/remove columns
  • ProtectWindows : Helps protecting Workbook or Excel Application

Allow Edit Range

This is an exception given by Excel where user gets rights to make exception during protection and allow to set specific area in the protected range where user allows to do certain operations.

Prerequisites

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Worksheet Level

public static bool IsWorksheetContentProtected(Excel.Worksheet xlSheet)
{
	try
	{
		Excel.Range oRange = xlSheet.Cells;
		if (oRange.Worksheet.ProtectContents || oRange.Worksheet.ProtectDrawingObjects || oRange.Worksheet.ProtectScenarios)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	catch (Exception)
	{

		return false;
	}
}

Above code applies on worksheet level object because Contents, Drawing Objects available at worksheet level.

Validate Protection

public static bool IsWorkBookProtected(Excel.Workbook xlWorkBook)
{
	try
	{
		if (xlWorkBook.ProtectWindows || xlWorkBook.ProtectStructure)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	catch (Exception ex)
	{
		return false;
	}
}

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Next>>Basic but useful properties of Worksheet in Excel C# VSTO

Leave a Reply

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