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#?

You want to know how to this programmatically? if so, what parts of this do you need help with (creating the range; setting the validation as the range, or...). Or you just want to know how to do it manually? — Todd Main
– Todd Main, Commented Mar 22, 2010 at 8:38

Todd Main · Accepted Answer · 2010-03-22 18:18:53Z

2

+150

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);
}

answered Mar 22, 2010 at 18:18

Todd Main

29.2k11 gold badges85 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anonymous Over a year ago

It's work, however I need 4 hours before accept this as correct answer. Furthermore I have another relate question here stackoverflow.com/questions/2497531/…

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related