none
VSTO Excel Drop Down RRS feed

  • Question

  • Hi,

    I've been looking for a way to have a drop down in a cell in an excel workbook template within a VSTO Excel Document level project.

    The requirements are:

    1. Drop down is bound to a web service
    2. Web service accepts a value from the row as a parameter to query data
    3. Drop down cell behaves like other cells when columns are moved, sorted, filtered

    I'm really stuck.  I haven't been able to find many resources online that give examples.  I also haven't been able to find many (any?) books about VSTO programming.

    Any help would be appreciated.

    Chris Stockmann

    • Moved by Youen Zen Monday, January 28, 2013 5:39 AM Appropriate forum for this issue
    Friday, January 25, 2013 2:31 PM

Answers

  • Hi Chris,

    Please try following snippet:

                try
                {
                    Excel.Worksheet sh = Application.ActiveWorkbook.ActiveSheet;
                    List<string> list = new List<string> { "A", "B", "C" };
                    StringBuilder builder = new StringBuilder();
                    int i = list.Count;
                    foreach (string s in list)
                    {
                        builder.Append(s);
                        i--;
                        if (i > 0)
                            builder.Append(",");
                    }
                    sh.Range["D1"].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing,
                        Excel.XlFormatConditionOperator.xlBetween, builder.ToString());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

    Have a good day,

    Tom

     

    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 1, 2013 7:34 AM
    Moderator

All replies

  • I assume you will be moved by a moderator to this forum.

    http://social.msdn.microsoft.com/Forums/en-US/vsto

    Otherwise visit it yourself.


    Success
    Cor

    Sunday, January 27, 2013 12:35 PM
  • Hi Chris,

    Thanks for posting in the MSDN Forum.

    According to your description, I would recommend you use validation interface in Excel Object model to approach your goal. Or create a Excel Document to add Winform's dropdown control into you document.

    However you need let me know which way you want to go, and declare your Office version, Visual Studio version first.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 28, 2013 5:37 AM
    Moderator
  • Hi Tom,

    Thank you for getting back to me.  I would prefer to use the excel validation interface if possible.

    The version of Office we are using is 2010 and the version of visual studio is also 2010.

    Thanks again for your assistance.

    Chris Stockmann

    Monday, January 28, 2013 1:44 PM
  • Hi Chris,

    Please try following snippet:

                try
                {
                    Excel.Worksheet sh = Application.ActiveWorkbook.ActiveSheet;
                    List<string> list = new List<string> { "A", "B", "C" };
                    StringBuilder builder = new StringBuilder();
                    int i = list.Count;
                    foreach (string s in list)
                    {
                        builder.Append(s);
                        i--;
                        if (i > 0)
                            builder.Append(",");
                    }
                    sh.Range["D1"].Validation.Add(Excel.XlDVType.xlValidateList, Type.Missing,
                        Excel.XlFormatConditionOperator.xlBetween, builder.ToString());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

    Have a good day,

    Tom

     

    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 1, 2013 7:34 AM
    Moderator