Articles in this section
Category / Section

How to protect Excel columns based on a drop down value from a cell?

3 mins read

Syncfusion Essential XlsIO is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can protect columns based on a drop-down value from a cell using Data Validation feature in C# and VB.NET.

A cell can be protected based on a value from another cell using data validation by restricting cells for modification, until a data validation formula that has dependent cell is satisfied. Refer to the following steps to protect an entire column cell values based on a drop-down value from a cell.

Steps to protect columns using data validation in Excel programmatically:

  1. Create a new C# console application project.

 

Create empty console application in visual studio

 

Create console application

 

  1. Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.Org.

 

Add XlsIO reference to the project

 

Install NuGet packages

 

  1. Include the following namespaces in the program.cs file.

C#

using Syncfusion.XlsIO;

 

VB.NET

Imports Syncfusion.XlsIO

 

  1. Use the following code snippet to protect the column B, C, or D based on a drop-down value selected from a cell in the Excel file.

C#

//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    worksheet["A1"].Text = "Select Column";
 
    //Add data validation in Cell A1
    IDataValidation list = worksheet["A1"].DataValidation;
    //Set dropdown validation type
    list.AllowType = ExcelDataType.User;
    list.ListOfValues = new string[] { "Column B", "Column C", "Column D" };
 
    //Data validation for Column B
    list = worksheet["B1"].EntireColumn.DataValidation;
    //Set formula validation type
    list.AllowType = ExcelDataType.Formula;
    list.FirstFormula = "$A$1=\"Column B\"";
 
    //Data validation for Column C
    list = worksheet["C1"].EntireColumn.DataValidation;
    //Set formula validation type
    list.AllowType = ExcelDataType.Formula;
    list.FirstFormula = "$A$1=\"Column C\"";
 
    //Data validation for Column D
    list = worksheet["D1"].EntireColumn.DataValidation;
    //Set formula validation type
    list.AllowType = ExcelDataType.Formula;
    list.FirstFormula = "$A$1=\"Column D\"";
 
    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}

 

VB.NET

'Instantiate the spreadsheet creation engine
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    worksheet("A1").Text = "Select Column"
 
    'Add data validation in Cell A1
    Dim list As IDataValidation = worksheet("A1").DataValidation
    'Set dropdown validation type
    List.AllowType = ExcelDataType.User
    list.ListOfValues = New String() {"Column B", "Column C", "Column D"}
 
    'Data validation for Column B
    list = worksheet("B1").EntireColumn.DataValidation
    'Set formula validation type
    list.AllowType = ExcelDataType.Formula
    list.FirstFormula = "$A$1=""Column B"""
 
    'Data validation for Column C
    list = worksheet("C1").EntireColumn.DataValidation
    'Set formula validation type
    list.AllowType = ExcelDataType.Formula
    list.FirstFormula = "$A$1=""Column C"""
 
    'Data validation for Column D
    list = worksheet("D1").EntireColumn.DataValidation
    'Set formula validation type
    list.AllowType = ExcelDataType.Formula
    list.FirstFormula = "$A$1=""Column D"""
 
    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

 

A complete working example to protect columns based on a drop-down value from a cell using data validation in Excel can be downloaded from Protect Columns using Data Validation.zip.

Learn more about Essential XlsIO through the documentation, where you will find supported features like data validation and formula with respective code examples.

Refer here to explore the rich set of Syncfusion Essential XlsIO features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied