C# excel data validation drop down list throwserror

user_7888137 0
Viewed 3661

I'm trying to add drop down list to a range using c#.

This is what I have done so far.

 Worksheet ws = PPTAddIn.thisAddin2Obj.Application.ActiveWorkbook.ActiveSheet;
            ws.get_Range("a1").Validation.Delete();
            ws.get_Range("a1").Validation.InCellDropdown = true;
            ws.get_Range("a1").Validation.IgnoreBlank = true;
            ws.get_Range("a1").Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertWarning, "opt1,opt2,opt3", Missing.Value);

at 3rd line of code it throws below exception
Exception from HRESULT: 0x800A03EC

ErrorImage

this is the stack trace 

   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)    at Microsoft.Office.Interop.Excel.Validation.set_InCellDropdown(Boolean ) at MS.ProductionPlanningTool.Excel.Ribbon_PPT.ribbon_signin_Click(Object sender, RibbonControlEventArgs e) in D:\MidasCloud\CloudTFS\ProductionPlanning\MSP2\MS.ProductionPlanningTool.Excel\UI\Ribbon_PPT.cs:line 1328    at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ControlActionRaise(IRibbonControl control)    at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ButtonClickCallback(RibbonComponentImpl component, Object[] args)    at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.Invoke(RibbonComponentCallback callback, Object[] args)    at Microsoft.Office.Tools.Ribbon.RibbonMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)    at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.System.Reflection.IReflect.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
excel .net c#
4 Answers

Thanks to the accepted answer for getting me most of the way there, but alas it didn't work first off. There was a missing paramater, as Jayakrishnan correctly points out in the comments under that answer. Because future visitors like me may not look at those comments for a while, I'll post my code here. My code shows the usage of named parameters to skip over the optional operator parameter. It also shows how to add data validation as a function taking any list of strings (I just do a .join beforehand!). Enjoy.

public void SetDropdownValidation(params string[] values)
{
    string valuesJoined = string.Join(",", values);
    Validation validation = RawRange.Validation;
    validation.Delete();
    validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertWarning, Formula1: valuesJoined, Formula2: Missing.Value);
    validation.InCellDropdown = true;
}

Notes

  • This function is written in the context of a class where RawRange is a member variable of type Range.
  • This code could be tweaked / made more flexible to deal with stuff like IgnoreBlank, but the basic idea is in the above
user_51347220
Related