How to protect Excel columns based on a drop down value from a cell?
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:
- Create a new C# console application project.
Create console application
- Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.Org.
Install NuGet packages
- Include the following namespaces in the program.cs file.
C#
using Syncfusion.XlsIO;
VB.NET
Imports Syncfusion.XlsIO
- 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.
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.
