CODEWITHSUNDEEP
c#excellistboxmulti-selectclosedxml
RBT
RBT

Reputation: 25917

Multi-select list box in an excel spreadsheet using data validation

I want to achieve a multi-select list box in an excel spreadsheet using data validation.

I found a link here which talks about developer tab in MS-excel where I can drag and drop a list box control to achieve the list box effect. Possibly that is the Visual Studio tools for Office (VSTO) route. I do not want to go this route.

Instead, I'm trying to achieve it through data validation and ClosedXML nuget package.

I already have below code with the help of which I can create a drop down but not sure how can I convert it into a multi-select list box:

//imported namespaces
using ClosedXML.Excel;
using System;
using System.Data;

//code in main method
var workbook = new XLWorkbook();

DataTable userData = new DataTable("Sheet1");
userData.Columns.Add("Master Column");


workbook.AddWorksheet(userData);
var worksheet = workbook.Worksheet(1);


 DataTable validationTable = new DataTable();
 validationTable.Columns.Add("DropDownItems");
 validationTable.TableName = "Sheet2";

 DataRow dr = validationTable.NewRow();
 dr["DropDownItems"] = "Item1";
 validationTable.Rows.Add(dr);

 dr = validationTable.NewRow();
 dr["DropDownItems"] = "Item2";
 validationTable.Rows.Add(dr);

 dr = validationTable.NewRow();
 dr["DropDownItems"] = "Item3";
 validationTable.Rows.Add(dr);


 workbook.AddWorksheet(validationTable);
 var worksheet2 = workbook.Worksheet(2);
 worksheet.Column(1).SetDataValidation().List(worksheet2.Range("A2:A4"), true);

  //keep the data validation worksheet hidden
  worksheet2.Hide();

  workbook.SaveAs(@"C:\myworkbook.xlsx");

Currently this source code results in a combo box like this:

enter image description here

I've to achieve multi-select behavior programatically using ClosedXML in C#.

Upvotes: 0

Views: 2571

Answers (2)

Francois Botha
Francois Botha

Reputation: 4849

Form controls are not supported by ClosedXML.

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21639

The Developer Tab doesn't require Visual Studio; it's just hidden away.

  • Go File → Options → on the left: Customize Ribbon.
  • Click on the Developer checkbox under the list of Main Tabs on the right.
  • Click OK.

Developer Options

A new 'menu' will appear on the ribbon called Developer, with categories like Code, Add-Ins, Controls, XML, Modify (varying depending on your version).

In Excel, you can't readily "convert" controls from one type to another like in Access etc, but with the Developers menu visible, you can now create a Listbox, and copy the VBA over from your Combo Box.

Developer Toolbar2

If you give the Listbox the same name the Combo Box had (after you delete the Combo Box) then the transition will be even easier. (If not there's always Ctrl+H Search & Replace!)

Upvotes: 1

Related Questions