CODEWITHSUNDEEP
c#excelcomoffice-interopole
Pankaj
Pankaj

Reputation: 2744

Getting Shape Type from Excel Shapes

I have an Excel Sheet which has some combo boxes and checkboxes.

I am trying to read their selected Values. My code works fine if there are combo boxes and crashes on Checkbox because there is no ListFillRange in them.

How do I get the shape type of the shapes so that my function GetControlValue can return the values of all the controls types.

foreach (Excel.Shape s in ws.Shapes)
{
    string value = GetControlValue(ws, s.Name);
}


private string GetControlValue(Excel.Worksheet ws, String shapeName)
{
    var selValue = ws.Shapes.Item(shapeName).ControlFormat.Value;

    //the fill range
    var r = ws.Shapes.Item(shapeName).ControlFormat.ListFillRange;
    var oRng = ws.Range[r];
    var selectedValue = oRng.get_Item(selValue).Value;
    return selectedValue;

}

There is one MsoShapeType enumeration as well but it always return msoFormControl

Upvotes: 1

Views: 2979

Answers (1)

user2140173
user2140173

Reputation:

MSForms controls

foreach (Shape s in ws.Shapes)
{
    //s.FormControlType.ToString();
}

ActiveX controls 

foreach (Shape s in ws.Shapes)
{
    //s.OLEFormat.progID.ToString();
}

Even though they are shapes in Excel, they are actually all OLEObjects for C# and it's better to treat them that way too (less casting cause COM treats them originally as Ole Objects, Shape is Excel's convenience etc). In case you wondered;

foreach (OLEObject obj in ws.OLEObjects())
{
    // obj.progID.ToString();
}

You probably need a switch statement next so you know which control you're dealing with as you have aptly noticed the checkbox does not have a ListFillRange property but Value and Labels have Caption instead of value etc.

Upvotes: 2

Related Questions