0

I am creating several DropDowns in Excel sheet using ExcelDNA, NetOffice and C#. Dropdowns are shown correctly, however I can't figure out how to bind an event each time the selection is changed. As you notice bellow I am binding to SheetChange event as suggested here http://www.c-sharpcorner.com/uploadfile/vasanthks/excel-dropdownlist-change-event-using-vba/ Any help would be appreciated. 

for (int i = 1; i < col + 1; i++)
{
      var comboRange = ActiveSheet.Cells[2, i];

            var combobox1 = currentSheet.Shapes.AddFormControl(XlFormControl.xlDropDown,
                Convert.ToInt32(comboRange.Left), Convert.ToInt32(comboRange.Top), Convert.ToInt32(comboRange.Width), Convert.ToInt32(comboRange.Height));
            combobox1.Name = "cmb_" + i;
            combobox1.Placement = XlPlacement.xlMoveAndSize;
            combobox1.ControlFormat.DropDownLines = 1;
            combobox1.ControlFormat.AddItem("Item1", 1);

            combobox1.Application.SheetChangeEvent += new Excel.Application_SheetChangeEventHandler(ComboBox_Changed);

        }
    }


    private void ComboBox_Changed(object sender, Excel.Range rng)
    {
        MessageBox.Show("Test", "Error",
                MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
Community
  • 1
  • 1
Jim
  • 2,760
  • 8
  • 42
  • 66

1 Answers1

1

Control type you use- XlFormControl.xlDropDown- doesn't provide any solution for working with events. Instead you should use OleObject control. Here is a code snippet which could be a starting point:

var combobox1 = currentSheet.Shapes.AddOLEObject(ClassType:="Forms.ComboBox.1",...

For more information see this link. Next you should try to add ComboBox1_Change() event.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • Hi Thanks, I have managed to convert combboxes to OleObject and raise events using : cb.Change += new MSForms.MdcComboEvents_ChangeEventHandler(CmdBtn_Click); However, I still strugling to detect which combobox is firing event and its value, any suggestion? – Jim May 05 '14 at 17:13