How do I assign a macro to my checkbox?

Posted on 2008-10-24
Medium Priority
Last Modified: 2013-11-17
I'm creating an Excel spreadsheet with C# and Excel Interop. I want to add a checkbox and assign it to an existing macro
As far as I googled, this is the way to do it, but the try-catch fails with the following message:

{"Unable to set the OnAction property of the OLEObject class"}      System.Exception {System.Runtime.InteropServices.COMException}

Also the change of the caption is not effective until the component gets clicked in Excel...?!
// Creating the checkbox
Shape checkBox = scSheet.Shapes.AddOLEObject("Forms.CheckBox.1", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, volumeCheckBoxCursor.Left, volumeCheckBoxCursor.Top, volumeCheckBoxCursor.Width, volumeCheckBoxCursor.Height);                        
// Getting a reference
OLEObject sheetBox = (OLEObject)scSheet.OLEObjects(checkBox.Name);            
// Changing the caption (simple and straight forward huh?)
sheetBox.Object.GetType().InvokeMember("Caption", System.Reflection.BindingFlags.SetProperty, null, sheetBox.Object, new object[] { "My Checkbox" });
// Now I want to assign a macro
    sheetBox.OnAction = "runMyMacro";
catch (Exception ex)
    //...always fails

Open in new window

Question by:jenmat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22797467
I've run across similar problems recently when trying to add a macro to option buttons I create at various points.

Go to Tools->Macro->Macros...
and make sure the Macro name is being referenced correctly.

For example, in my problem I was calling macro FilterOnOff.  Excel didn't recognize it because the full name listed was LogModules.FilterOnOff, as it was in a module named LogModules.

You may also need to reference the workbook the module is in if it is not in the workbook where the code is being run:

sheetBox.OnAction = "'" & Workbooks("NameofWorkbook").Name & "'!runMyMacro"

Notice you have to encapsulate the workbook name with single quotes and seperate it from the macro name with an exclamation point.

LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22797515
If the workbook you want to reference is the active workbook, then try

sheetBox.OnAction = "'" & ActiveWorkbook.Name & "'!runMyMacro"

Again, you may not have the full name of the macro, so check the macro list to make sure.

LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22797721
Also, I just noticed it seems you are adding ActiveX checkboxes, which are from the Control ToolBox, not checkboxes from the Forms toolbar.  I'm not sure if you can use the OnAction property with those objects.  They use Event Handling, such as Click() event.

You might have to switch to using the other type of checkbox.

dim myRng as Range
Dim chkBox As checkBox
Set myRng = Range("Your range here")
With myRng
     Set chkBox = .Parent.CheckBoxes.Add(Top:=.Top, Left:=.Left, Height:=.Height, Width:=.Width)
End With
With chkBox
     .OnAction = 
 "'" & ActiveWorkbook.Name & "'!runMyMacro"
     'Whatever other options you want to set
     .Caption = ""
End With

Open in new window

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


Author Comment

ID: 22799372
I'd be happy to use forms checkboxes instead, but how is it done via Interop?
Observe that I'm not adding checkboxes or code from VBA in a macro but from .Net.

I've tried sheet.CheckBoxes.Add(...
but I get "Checkboxes() referenced as property when it is a method"...
LVL 18

Accepted Solution

Cory Vandenberg earned 500 total points
ID: 22799447
I'm afraid I can't answer that one, as I've never used .Net.

To stick with the OLEObject checkboxes, perhaps you can program the Click() event to call your macro.  For each checkbox created you would need to have the code:

Call runMyMacro

inside of the checkbox_Click() sub for that object.

I've never tried to program that, so I'm not sure if it's possible to edit the source code in such a manner.
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22799624
Not sure if this will help, but you can program the subs for each checkbox being added.  In Excel you have to make sure the Reference for Microsoft Visual Basic for Applications Extensibility 5.3 is turned on.  Once it is you can use the following to add Macros to the workbook.

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent
Set VBP = ThisWorkbook.VBProject
Set VBModule = VBP.VBComponents.Item("ActiveWorkbook").CodeModule
VBModule.AddFromString ("Sub " checkBox.Name & "_Click" & vbCrLf & "Call runMyMacro" & vbCrLf & "End Sub")

Open in new window


Author Closing Comment

ID: 31509673
The workaround with creating a click method worked out.

Still have to figure out why the checkbox text only updates after the component is clicked...


Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question