How to add number validation in MS Excel cell using C#

Question

My objective is to restrict user to enter values only between the range of 1 to 100 in MS Excel cell.

I am programmatically generating Excel files, but when I add above validation Exception is thrown as Exception from HRESULT: 0x800A03EC

code I've written is as below:

int[] arr = {1,100};
ExcelApp.get_Range(col1, col2).Cells.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, arr, Type.Missing);

In above code ExcelApp is an object of Microsoft.Office.Interop.Excel.ApplicationClass

Any help is really appreciated.

Check http://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error — Kamran Shahid, Dec 17 '12 at 05:21
I had viewed this question before asking mine, but problem given in that question is different than mine, I think I am doing something wrong while passing arguments to method Validation.Add(); — love Computer science, Dec 17 '12 at 05:27
Your code would be more concise if you added `using Microsoft.Office.Interop.Excel` to the top of the code file, and referred to the enumerations directly. — Zev Spitz, Dec 17 '12 at 09:31

Jeremy Thompson · Answer 1 · 2013-01-02T01:32:22.243

You need to delete the cell validator before adding another one. Otherwise you will see the validation Exception is thrown as Exception from HRESULT: 0x800A03EC

ExcelApp.get_Range("A1").Cells.Validation.Delete();

ExcelApp.get_Range("A1").Cells.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, delimitedString1To100, Type.Missing);

If no cell validator exists (ie first time) then Deleting doesn't cause a problem, its safe to leave in.

Edit/Solution:

The problem in the code was the variable arr contained two items 1 & 100. I'm guessing the XLFormatConditionOperator argument xlBetween in Validation.Add's parameter mislead us. To make it work for a argument XLDVType of xlValidateList the Formula1 argument needs to contain all the valid values 1,2,3...100:

var val = new Random();
var delimitedString1To100 = string.Join(",", (int[])Enumerable.Range(1, 100).ToArray());
for (int i = 1; i < 11; i++)
{
    using (var rnCells = xlApp.Range["A" + i.ToString()].WithComCleanup())
    {
        rnCells.Resource.Value2 = val.Next(100);
        rnCells.Resource.Cells.Validation.Delete();
        rnCells.Resource.Cells.Validation.Add(
            Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
            Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertInformation,
            Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, delimitedString1To100, Type.Missing);
    }
}

still giving the same exception as "Exception from HRESULT: 0x800A03EC". Passing "(col1, col2)" as arguments for get_Range() is not an issue, as both variable contains cell addresses. — love Computer science, Jan 01 '13 at 11:45
Please see my edit or try out the solution here http://temp-share.com/show/KdPf0mC9h (it contains the VSTO.Contrib ref's). Thanks for the bounty! — Jeremy Thompson, Jan 02 '13 at 01:08
I know this is late but did it solve the problem? Apologies for the late edit after the bounty was awarded automatically, but this solution did work for me *and atleast 3 others*. Care to accept? Cheers — Jeremy Thompson, Sep 05 '16 at 14:34

Zev Spitz · Answer 2 · 2012-12-18T10:53:53.677

I don't think you can pass in a .NET array of integers. You need to pass in a comma-separated string, or a string reference to a worksheet range. From the docs:

xlValidateList - Formula1 is required; Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list.

For example:

ExcelApp.get_Range(col1, col2).Cells.Validation.Add(
    XlDVType.xlValidateList, 
    XlDVAlertStyle.xlValidAlertInformation, 
    XlFormatConditionOperator.xlBetween, 
    "1,100"
);

score 0 · Answer 3 · answered Dec 28 '12 at 10:30

what you want can be done by using macro, which can be assigned on cell value change. for incoperating macro to dynamically created excel visit http://www.eggheadcafe.com/articles/create_macro_at_runtime_in_dotnet.asp . i implemented the same sometimes ago and it is easier also.

How to add number validation in MS Excel cell using C#

3 Answers3

Edit/Solution:

Linked