Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 23. January 2011 15:51

Excel Data Validation provides a nice mechanism to help users select from a set of acceptable choices, by adding a drop-down directly in a cell and displaying the list of options when the cell is selected. To do that within Excel, just go to the Data ribbon, and the Data Validation button displays a dialog like the one below. Selecting allow “List”, and typing in a few comma-separated values in the Source section will do the job. How would we go about to do the same thing from .NET?

DataValidationDialog

Turns out it’s not very complicated, as I just found out. Just create a Validation object, Add it to a Range, and you are good to go. Here is a code snippet to do just that, from a VSTO project:

var excel = Globals.ThisAddIn.Application;
var worksheet = (Worksheet)excel.ActiveSheet;
         
var list = new List<string>();
list.Add("Alpha");
list.Add("Bravo");
list.Add("Charlie");
list.Add("Delta");
list.Add("Echo");

var flatList = string.Join(",", list.ToArray());

var cell = (Range)worksheet.Cells[2, 2];
cell.Validation.Delete();
cell.Validation.Add(
   XlDVType.xlValidateList,
   XlDVAlertStyle.xlValidAlertInformation,
   XlFormatConditionOperator.xlBetween,
   flatList,
   Type.Missing);

cell.Validation.IgnoreBlank = true;
cell.Validation.InCellDropdown = true;

Nothing fancy, but as usual it took a bit of searching to figure out the right enumerations to use in the method call – hopefully it will be useful to someone else!

In the process, I found out two things. First, I wondered what would happen if I tried to set through code the contents of a cell to a value that isn’t valid. The answer is, Data Validation doesn’t validate anything in that case – it appears to be strictly a UI mechanism. Then, I realized that I had no clear idea what the 2nd and 3rd tab in the dialog do; turns out, these are potentially pretty cool. Input Message behaves like a ToolTip that shows up on cell selection, with a title and message, in a way similar to Comments, but not editable. Error Alert defines the message that should show up when an invalid value is entered – and allows to disable the Error Alert if need be. So if all you wanted was a DropDown with “suggested” choices, you could just disable the error alert, and you would have a cell with a DropDown, where users could still type any freeform text they please.

Comments

1/27/2011 6:44:01 AM #

jimmy page

Love it!  Excel is the best.  If I could have Excel for breakfast I would.
-joe

jimmy page United States | Reply

2/18/2011 4:23:34 PM #

trackback

Codebix.com - Your post is on Codebix.com

This post has been featured on Codebix.com. The place to find latest articles on programming. Click on the url to reach your post's page.

Codebix.com | Reply

3/6/2011 9:47:28 PM #

A.P.

Great thanks!

A.P. Australia | Reply

8/1/2011 4:33:38 PM #

Sreenivas

Hi Mathias,

i have seen usefule posts from you..thanks.

I have one question regarding Validation list. The list is being added with out any issue, but when the workbook saved and re-opened, i am seeing error as "un readable content found...".
Do you have any idea?

Thanks
Sreenivas

Sreenivas India | Reply

1/31/2012 2:31:19 AM #

Greg

Maybe someone got an idea how to to this with Silverlight AutomationFactory?

Greg Germany | Reply

2/19/2012 10:14:44 PM #

Hasan

Thanks Alot for the useful information

Hasan Jordan | Reply

9/27/2012 6:02:24 PM #

satyajit

hi
i am populatingthe list from sql database
and this code fails if i fetch anything more than 27 records.

can you guide me where i am wrong

satyajit India | Reply

1/1/2013 3:23:30 PM #

namdeoo

I would like to know how to save the data from drop down list into database.Please give me suggestion.

namdeoo Macedonia (FYROM) | Reply

1/23/2013 3:23:03 AM #

Leonardo Salmaso

Bro do you know how I can subscribe to dropdownlist selection changed event????

Leonardo Salmaso Argentina | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS