none
Accessing Excel ComboBox Shape from c# RRS feed

  • Question

  • I have an excel spreadsheet that I load, I can iterate through the various shapes to find the one I am looking for, a ComboBox called "cboYear", but how can I get a reference to the object so I can add items to it?

    Microsoft.Office.Interop.Excel.Shape excelShape; for (int x = 1; x <= excelSht.Shapes.Count; x++) { excelShape = excelSht.Shapes.Item(x); if (excelShape.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject) { if (excelShape.Name.Equals("cboYear", StringComparison.CurrentCultureIgnoreCase)) { //Console.WriteLine("Excel shape: " + excelShape.GetType() + " " + excelShape.Type.ToString() + " " + excelShape.OLEFormat + " " + excelShape.ControlFormat + " Name: " + excelShape.Name); //Output System.__ComObject msoOLEControlObject System.__ComObject System.__ComObject Name: cboYear

    //get instance of control here } } }

    Any help most appreciated!

    Tuesday, May 30, 2017 1:56 AM

All replies

  • Hi Hayden Fisher,

    Thank you for posting here.

    According to your question is more related to excel, I will move it to Excel for Developers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 30, 2017 2:50 AM
  • Hi Hayden Fisher,

    after finding the correct combo box shape , you can try to use ControlFormat.List Method.

    it Returns or sets the text entries in the specified list box or a combo box, as an array of strings, or returns or sets a single text entry. An error occurs if there are no entries in the list.

    Reference:

    ControlFormat.List Method (Excel)

    Example:

      Excel.Shapes theShapes = this.Shapes;
    
                foreach (Excel.Shape aShape in theShapes)
                {
                    foreach (var groupItem in aShape.GroupItems)
                    {
                        var s = (Excel.Shape)groupItem;
                        if (s is Excel.OLEObject) continue;
    
                        if (s.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl)
                        {
                            if (s.FormControlType == Excel.XlFormControl.xlDropDown)
                            {
                                Console.WriteLine("### " + s.Name);
                                s.ControlFormat.List = excelsheet.Range("A1:A4").Value;
                            }
                        }
                    }
                }

    this example is just to understand the logic. you can try to modify it as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 31, 2017 6:59 AM
    Moderator