none
Accessing Excel ComboBox values programmatically

    Question

  • All,

    I have created an Excel add-in that loads information from Microsoft Project Data Server into a spreadsheet.  For each row of data added to spreadsheet, I add a combobox that is populated with several options to choose from (there is a different default value for each combobox).  I use the following code snippet to add the comboboxes to my spreadsheet:

    Microsoft.Office.Tools.Excel.Controls.ComboBox comboBox1 = this.Controls.AddComboBox(this.Range["C"+(i+7), missing], "comboBox"+i);

    comboBox1.Items.Add(""); //index 0

    comboBox1.Items.Add("blah"); //index 1

    comboBox1.Items.Add("blah blah"); //index 2

    comboBox1.SelectedIndex = 0;

    I have two questions: how does one programmatically add an array of comboboxes to a spreadsheet and how does one then programmatically access the values within those comboboxes.

    Any help would be appreciated.

    I am using Excel 2003 with VSTO 2005 and Visual Studio 2005.

    Thanks,

    Kevin

     

    Wednesday, January 03, 2007 10:13 PM

Answers

  • To get the selected value in any given ComboBox, use the SelectedItem property of the ComboBox. For more information about this property, see http://msdn2.microsoft.com/en-us/library/system.windows.forms.combobox.selecteditem.aspx. For example, the following code displays the selected value in each ComboBox:

                Microsoft.Office.Tools.Excel.Controls.ComboBox tempComboBox;

                for (int i = 1; i <= 3; i++)
                {
                    tempComboBox = this.Controls["comboBox" + i.ToString()] as
                        Microsoft.Office.Tools.Excel.Controls.ComboBox;

                    if (tempComboBox != null)
                    {
                        if (tempComboBox.SelectedItem != null)
                        {
                            MessageBox.Show(tempComboBox.SelectedItem.ToString());
                        }
                        else
                        {
                            MessageBox.Show("No item is selected.");
                        }
                    }
                }

    Note that the ComboBox control is a Windows Forms control, and the way that you access the selected value has nothing to do with VSTO - the Microsoft.Office.Tools.Excel.Controls.ComboBox class that you use on the worksheet derives most of its behavior, including the SelectedItem property, from System.Windows.Forms.ComboBox. For more assistance with using the ComboBox control (or any other Windows Forms control), your best bet is to ask your question in the Windows Forms forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=8&SiteID=1.

    I hope this helps,

    McLean Schofield

    Thursday, January 04, 2007 9:41 PM
    Answerer

All replies

  • BTW:  I am using c# and I am adding the ComboBoxes using a for loop as follows (Note: the code is slightly changed from above):

    for (int i=0; i<someVariableInteger; i++)

    {

    this.Controls.AddComboBox(this.Range["C"+(i+7), missing], "comboBox"+i);

    //comboBox1.Items.Add(""); //index 0

    //comboBox1.Items.Add("blah"); //index 1

    //comboBox1.Items.Add("blah blah"); //index 2

    //comboBox1.SelectedIndex = 0;

    }

    This has the desired effect of adding uniquely named comboboxes to the spreadsheet but now I can't seem to add items.  I saw some code in this forum using VB but have no idea how to translate it into c#. Here is the relevant VB code snippet:

         For i As Integer = 1 To Me.Controls.Count
            CType(Me.Controls("ComboBox" & i), Windows.Forms.ComboBox).Items.Add("Line")
            CType(Me.Controls("ComboBox" & i), Windows.Forms.ComboBox).Items.Add("Case-to-Case")
        Next

    PLEASE HELP!

    Thank you,

    Kevin

    Thursday, January 04, 2007 4:53 PM
  • Hi Kevin

    This question would better be asked in the excel.programming newsgroup, where the experts on the Excel object model reside. FWIW, I don't think it is possible to add controls to a worksheet in the form of an array. I think each needs to be added and populated individually. Of course, you can consolidate the code into a single method that you call as often as required... But you should definitely double-check this with the specialists in the newsgroup.

    Thursday, January 04, 2007 5:13 PM
    Moderator
  • Thanks for the tip Cindy.

    However, the excel.programming newsgroup is primarily a VBA forum and they have no clue what I'm talking about. All I am trying to do is use c# within an Excel add-in written with VSTO 2005 to access the current value in the combobox in each row.  Each combobox has a unique name in the form of comboBox+i, where i is an integer assigned via a for loop. When I click on a button embedded in the worksheet I want to iterate through each row, pick up the value and then update Project Server.  I can do all of the above except for access the current selected value in the combobox.  There must be some way to dynamically pick this information out using a for loop and some way to dynamically reference the combobox object using the variable name (comboBox+i).  I am fairly new to c# and am not familiar with the ins and outs of dealing with collections (I suspect this is the only way I can access the information but I don't know the syntax or the underlying concept very well).

    Thanks again for the response.

    Thursday, January 04, 2007 5:41 PM
  • Hello,

    The VB code that you posted is on the right track. To access any control on the worksheet, you can pass in the name of the control that you want into the this.Controls property. The following code will loop through all of the ComboBox controls on the worksheet and add items to each control. This example assumes that you have already added 3 ComboBox controls with the names "comboBox" + i (that is, comboBox1, comboBox2, etc.) to the current worksheet.

                Microsoft.Office.Tools.Excel.Controls.ComboBox tempComboBox;

                for (int i = 1; i <= 3; i++)
                {
                    tempComboBox = this.Controls["comboBox" + i.ToString()] as
                        Microsoft.Office.Tools.Excel.Controls.ComboBox;

                    if (tempComboBox != null)
                    {
                        tempComboBox.Items.Add("Line");
                        tempComboBox.Items.Add("Case-to-Case");
                    }
                }

    I hope this helps.

    McLean Schofield

    Thursday, January 04, 2007 6:47 PM
    Answerer
  • Thanks McClean,

    It helps for generating the comboboxes and the attendant items but I can't seem to extend the concept to access the selected value within the combo box. Any ideas?

    Thursday, January 04, 2007 7:06 PM
  • Hi Kevin

    <argh> I don't know where my head was when I read your original message. Apologies. For some reason, I thought you were working with native Excel controls (Form controls or ActiveX controls).

    I see that McLean has answered you :-) 

    Thursday, January 04, 2007 7:12 PM
    Moderator
  • To get the selected value in any given ComboBox, use the SelectedItem property of the ComboBox. For more information about this property, see http://msdn2.microsoft.com/en-us/library/system.windows.forms.combobox.selecteditem.aspx. For example, the following code displays the selected value in each ComboBox:

                Microsoft.Office.Tools.Excel.Controls.ComboBox tempComboBox;

                for (int i = 1; i <= 3; i++)
                {
                    tempComboBox = this.Controls["comboBox" + i.ToString()] as
                        Microsoft.Office.Tools.Excel.Controls.ComboBox;

                    if (tempComboBox != null)
                    {
                        if (tempComboBox.SelectedItem != null)
                        {
                            MessageBox.Show(tempComboBox.SelectedItem.ToString());
                        }
                        else
                        {
                            MessageBox.Show("No item is selected.");
                        }
                    }
                }

    Note that the ComboBox control is a Windows Forms control, and the way that you access the selected value has nothing to do with VSTO - the Microsoft.Office.Tools.Excel.Controls.ComboBox class that you use on the worksheet derives most of its behavior, including the SelectedItem property, from System.Windows.Forms.ComboBox. For more assistance with using the ComboBox control (or any other Windows Forms control), your best bet is to ask your question in the Windows Forms forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=8&SiteID=1.

    I hope this helps,

    McLean Schofield

    Thursday, January 04, 2007 9:41 PM
    Answerer
  • Thank you for your help... That worked like a charm.
    Thursday, January 04, 2007 10:28 PM