How to add a checkbox control to an Excel cell pro

Question

I am using the Excel COM object in C# and want to insert a checkbox dynamically to an Excel sheet and make it checked or unchecked based on a condition.

OR

how can i mark as checked an existing checkbox in the Excel sheet programatically. I have looked around and I didn't find any solution.

Answer 1

You can always record a macro in MS Excel and it will give you a good idea of what needs to be done with Excel object in order to achieve something. For example, when recording macro for your problem, it came out with the following code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=65.25, Top:=24, Width:=108, Height:=21). _
        Select

I hope that from here you can see what needs to be done to insert checkbox on the active sheet.

Here is more detailed explanation (Visual Studio 2010 and C#): 1. Fire up Visual Studio and create new project (windows app or console app) 2. Right click on References and select "Add Reference" 3. Select COM references and add Microsoft Excel xx.x Object Library (in my case xx.x is 14.0 which is Excel 2010). 4. Somewhere in your code (some function like Main or some click on a button) add this code:

// Start excel
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

// Get a sheet 
Microsoft.Office.Interop.Excel._Workbook oWB = (Microsoft.Office.Interop.Excel._Workbook)oXL.Workbooks.Add(System.Reflection.Missing.Value);
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

// Get ole objects and add new one
Microsoft.Office.Interop.Excel.OLEObjects objs = oSheet.OLEObjects();

// Here is the method that is posted in the answer
Microsoft.Office.Interop.Excel.OLEObject obj = objs.Add("Forms.CheckBox.1", 
    System.Reflection.Missing.Value,
    System.Reflection.Missing.Value,
    false,
    false,
    System.Reflection.Missing.Value,
    System.Reflection.Missing.Value,
    65.25,
    24,
    108,
    21);
// Here, you are making it checked. obj.Object is dynamic, so you will not get help from visual studio, but you know what properties CheckBox can have, right?
obj.Object.Value = true;

I hope this helps.