How to protect Excel columns based on a dropdown 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 dropdown value from a cell using the 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 with a dependent cell is satisfied. Refer to the following steps to protect entire column cell values based on a dropdown 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 a 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 dropdown 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 dropdown 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.
Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.
Starting with v16.2.0.x, if you reference Syncfusion® assemblies from a trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message.
Conclusion
I hope you enjoyed learning about how to protect Excel columns based on a dropdown value from a cell.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
