How to create validation from name range on another worksheet in excel using C#?

Question

I have create name range on sheet "A" so I need to use this range as validation ComboBox on sheet B. I want to know how can I setting validation as range using C#?

Answer 1

The sheets won't matter because you'll just reference the named range of the list value range. Here you go below (assumes Interop) - listValidatingRange is where you have your values that need to be displayed in the dropdown - add that as a named range. cellThatNeedsValidating is the cell that you want the drop-down to appear in - add that as a named range. Then, on cellThatNeedsValidating, add the validation to be that of "=ListValidatingRange".

private void SetValidation()
{

    Microsoft.Office.Tools.Excel.NamedRange listValidatingRange =
        this.Controls.AddNamedRange(this.Range[""C1:C13"", missing],
        "ListValidatingRange");

    Microsoft.Office.Tools.Excel.NamedRange cellThatNeedsValidating =
        this.Controls.AddNamedRange(this.Range[""A1"", missing],
        "cellThatNeedsValidating");

    cellThatNeedsValidating.Validation.Add(
        Excel.XlDVType.xlValidateList ,
        Excel.XlDVAlertStyle.xlValidAlertStop,
        missing, "=ListValidatingRange", missing);
}