65652

How to add a checkbox control to an Excel cell programatically or check or uncheck an existing check

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.

Answer1:

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.

Recommend

  • Excel VBA paste from external program
  • Using PrintOut for Adobe PDF with predefined path and file name
  • Change coordinates to a series of rasters with multiple layers in R
  • Excel VBA: Copying Pictures from image controls to activeX objects
  • Add specific Document to specific Word.Application
  • How to navigate user to a facebook page via Android's facebook api? [duplicate]
  • Export data from Excel to Access using VBA
  • vba paste values and keep source formatting?
  • Yahoo finance historical stock price power query returns 301 response
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • Angularjs pass function from Controller to Directive (or call controller function from directive) -
  • Webgrid not refreshing after delete MVC
  • Jquery UI tool tip close icon
  • Disable Enter in editText android
  • Change multiple background-images with jQuery
  • Algorithm for a smudge tool?
  • Lost migrations and Azure database is now out of sync
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Matplotlib draw Spline from multiple points
  • XCode can't find symbols for a specific iOS library/framework project
  • Calling of Constructors in a Java
  • Compare two NSDates in iPhone
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Use group_by to filter specific cases while keeping NAs
  • Android Studio and gradle
  • log4net write single file for each call to log.info
  • Benchmarking RAM performance - UWP and C#
  • python regex in pyparsing
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Qt: Run a script BEFORE make
  • How can I remove ASP.NET Designer.cs files?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • java string with new operator and a literal