C# Excel Add In -- Programatically Add In Memory Drop Down/Pick List

Question

Hey,

I have been experimenting with creating an Excel add in in C#. I was wondering if it is possible to programatically create a drop down list for a column where the items do not have to be in the worksheet? That is, I know it can be done using a data validation and a range of cells but I do not want the list of drop down items to be visible to the user/to be editable.

Any insight/links to aid in this would be greatly appreciated.

Thank you

Answer 1

You could use data validation, setting up the list in code, using something along these lines:

var worksheet = (Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
     var cell = (Range)worksheet.Cells[1, 1];
     cell.Validation.Add(
        XlDVType.xlValidateList, 
        XlDVAlertStyle.xlValidAlertStop, 
        XlFormatConditionOperator.xlBetween,
        "A, B, C, D, E");

This will create a data validation list with items A, B, C etc... These are still editable by the user through the validation menu, though.