Solved

How do I assign a macro to my checkbox?

Posted on 2008-10-24
7
3,267 Views
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

try

{

    sheetBox.OnAction = "runMyMacro";

}

catch (Exception ex)

{

    //...always fails

}

Open in new window

0
Comment
Question by:jenmat
  • 5
  • 2
7 Comments
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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:

e.g.
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.

WC
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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.

WC
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jenmat
Comment Utility
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"...
0
 
LVL 18

Accepted Solution

by:
WarCrimes earned 125 total points
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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

0
 

Author Closing Comment

by:jenmat
Comment Utility
The workaround with creating a click method worked out.
Thanks!

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

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now