none
Excel Controls - Delete Combo Box Dynamically RRS feed

  • Question

  • I've added a combo box to my worksheet dynamically, and I want to delete the combo box dynamically at the end of two events - specifically the combobox.LostFocus and combobox.KeyDown events.

    It currently works for two scenarios:

    1. The use manually selects an item from the dropdown
    2. The user partially types in an item and auto-completes fills in the rest, then the user hits enter on the keyboard.

    It does NOT work if the user types in the entire text for a select, and presses enter. I get the message:

    COMException was unhanded by user code: Unable to get the Name property of the OLEObject class

    Visual Studio points to the line 'sheetVSTO.Controls.Remove(myBox);' in the myBox_LostFocus event handler as the source of the failure.

    A user show be able to type the entire text into the combobox and press enter. Any help would be appreciated.

    Here is what I have (let me know if more detailed needed):

    private void AddComboBox(String[] list) { Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet; Microsoft.Office.Tools.Excel.Worksheet sheetVSTO = Globals.Factory.GetVstoObject(sheet); Excel.Range cell = Globals.ThisAddIn.Application.ActiveCell; Microsoft.Office.Tools.Excel.Controls.ComboBox myBox= new Microsoft.Office.Tools.Excel.Controls.ComboBox(); myBox.Name = "button1"; myBox.Items.AddRange(list); myBox.KeyDown += new KeyEventHandler(myBox_KeyDown); myBox.LostFocus += new EventHandler(myBox_LostFocus); myBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend; myBox.AutoCompleteSource = AutoCompleteSource.ListItems; myBox.Focus(); Microsoft.Office.Tools.Excel.ControlSite myBoxControl = sheetVSTO.Controls.AddControl(myBox, cell, boxName); } void myBox_KeyDown(object sender, KeyEventArgs e) { if (e.KeyCode == System.Windows.Forms.Keys.Enter) { Microsoft.Office.Tools.Excel.Controls.ComboBox myBox = sender as Microsoft.Office.Tools.Excel.Controls.ComboBox; Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet; if (myBox.SelectedIndex > -1) { Microsoft.Office.Tools.Excel.Worksheet sheetVSTO = Globals.Factory.GetVstoObject(sheet); sheetVSTO.Controls.Remove(myBox); } } }

        void myBox_LostFocus(object sender, EventArgs e)
        {
            Microsoft.Office.Tools.Excel.Controls.ComboBox myBox = sender as Microsoft.Office.Tools.Excel.Controls.ComboBox;
            Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
            Microsoft.Office.Tools.Excel.Worksheet sheetVSTO = Globals.Factory.GetVstoObject(sheet);
            sheetVSTO.Controls.Remove(myBox);
        }


    • Edited by wtt235 Wednesday, September 26, 2012 6:44 PM
    Wednesday, September 26, 2012 2:06 PM

All replies

  • I've found that the code is not compilable. I was told by the compiler that Microsoft.Office.Tools.Excel.Controls.ComboBox does not contain a defination for Name. I've added the Microsoft.Office.Tools.Excel into reference. Can you show the other part of the code ?

    Monday, October 1, 2012 12:04 PM