Custom Validation to protect cells structure Excel Addin C#

Custom Validation to protect cells structure Excel Addin C#

Protect:

In Excel there are different types of protection which serves different purpose based on different needs. In this article I will cover how to protect specific cell range using Custom Validation using VSTO C# code. What custom validation offers? It provides basic protection which doesn’t need any password unlike Protect method (Please refer my previous post how to protect cells here)

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

Code example:

private void btnCustomValidation_Click(object sender, RibbonControlEventArgs e)
{
	//Retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//check if already validation added
	if (oRange.Locked)
	{
		//Delete existing validation
		oRange.Validation.Delete();
		//Mark locked true
		oRange.Locked = true;
		//Add validation
		oRange.Validation.Add(excel.XlDVType.xlValidateCustom, Type.Missing, Type.Missing, "\"\"");
	}
	else
	{
		//Mark locked true
		oRange.Locked = true;
		//Add validation
		oRange.Validation.Add(excel.XlDVType.xlValidateCustom, Type.Missing, Type.Missing, "\"\"");
	}
}

Considering below screenshot where i want user to make edit only number not my headers, the easiest way I do by adding custom validation. When user will try to make changes to header, he will be prompted with validation error. Point to be noted : delete operation cannot be prevented by Custom Validation.

Before code run the data would look like below where I will try to make changes in Column “E” Row 1 :

Post code rung you will notice that I tried typing something in column “E” and custom validation gets fired as shown below:

Next : AddSignatureLine in Excel Worksheet C#

Leave a Reply

Your email address will not be published.