none
INDIRECT function and using DataValidation Dynamic drop-downlist box RRS feed

  • Question

  • Hello,

    I'd like to have a dynamic drop-downlist box that's source property has the "Indirect" function. My code is written below;

    formula1 = @"=INDIRECT(E" + rowNumber.ToString() + "&" + @"""_Sheet3" + @""")" --this is my dynamic formula (it depends on "E" column+RowIndex+"_Sheet3" string which refer a name) to fill dropdown list box dynamically for the cell variable

     

     

     

    var cell = (Excel.Range)worksheet.Cells[rowNumber, column];

    formula1 = @"=INDIRECT(E" + rowNumber.ToString() + "&" + @"""_Sheet3" + @""")" 

    cell.Validation.Delete();

    cell.Validation.Add(Excel.XlDVType.xlValidateList,Excel.XlDVAlertStyle.xlValidAlertStop,Type.Missing, formula1, Type.Missing);

     

    whenever .Add code line executes, compiler throws a ComException (Exception from HRESULT:0x800A03EC). Could you tell me what is wrong or right method ?

     



    • Edited by Hak_B Wednesday, December 14, 2011 9:41 AM
    Wednesday, December 14, 2011 8:27 AM

Answers

  • Hi Hak_B,

     

    Thanks for post in the MSDN Forum.

     

    I think you have wrong parameter in the function “INDIRECT”. As far as I known the Exception HREESULT:0x800A03EC means that the function you used has thrown out an exception. It’s based on my experience that the second parameter “_Sheet3” is incorrect. I would recommend you use “true” instead of it.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 15, 2011 10:20 AM
    Moderator
  • Hi Hak_B,

     

    OK, it will not get this exception if it like you said. I would recommend you check the formula string to see whether there have incorrect characters.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 11:17 AM
    Moderator

All replies

  • Hi Hak_B,

     

    Thanks for post in the MSDN Forum.

     

    I think you have wrong parameter in the function “INDIRECT”. As far as I known the Exception HREESULT:0x800A03EC means that the function you used has thrown out an exception. It’s based on my experience that the second parameter “_Sheet3” is incorrect. I would recommend you use “true” instead of it.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 15, 2011 10:20 AM
    Moderator
  • Thanks for your your reply Tom. But "_Sheet3" is not second parameter. "_Sheet3" is a part of formula's first parameter. I know indirect function comprise 2 parts indirect(text or cell addres ; True-False) but I've just use first parameter not second. It is a part of name. I mean, let assume that I've a name abc_Sheet3 and it refers {x\y\z} and I'd like to form a formula which refers to abc_Sheet3. So, E2 refers abc plus _Sheet3 equals abc_Sheet3 after that indirect(abc_Sheet3) will refer to x,y,z so my drop dwon list contains x,y,z.


    Have a good day too you,
    • Edited by Hak_B Thursday, December 15, 2011 10:37 AM
    Thursday, December 15, 2011 10:32 AM
  • Hi Hak_B,

     

    OK, it will not get this exception if it like you said. I would recommend you check the formula string to see whether there have incorrect characters.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 11:17 AM
    Moderator