How to assign add-in code to a control button

[JenniferMurphy]

I have been testing a macro that I have assigned to a Button control in an .xlsm workbook. Now that OI have it working, I'd like to move it to an add-in module to other workbooks can use it. But once I move it to the add-in module, where I have lots of UDFs, the Assign Macro dialog doesn't offer the add-in as an option.

What do I have to do to get any workbook to be able to access this macro in the add-in module from a button control?

Thanks

 

[Gokhan Aycan]

Hey, does this answer your question?

StackOverflow

Summary,

Option Explicit

Private Sub CommandButton1_Click()
    Application.Run "MyAddIn.xlam!Sample()"
End Sub

 

[JenniferMurphy]

Hey, does this answer your question?

StackOverflow

Summary,

Option Explicit

Private Sub CommandButton1_Click()
    Application.Run "MyAddIn.xlam!Sample()"
End Sub

It sounds like the answer is that there is no way for a control in a workbook to directly access a macro in an installed add-in module. The work-around is to add a macro to a module in that workbook that then accesses the macro in the add-in. And this means that there is no way for a regular (.xlsx) workbook to access an add-in macro. It will have to become a macro-enabled (.xlam) workbook. Correct?

(sigh) M$ST giveth once and M$FT taketh away twice.

Thanks for the pointer.

 

[Fluff]

You can do it, but you need to specify the full path & macro name like
'C:\Users\username\AppData\Roaming\Microsoft\AddIns\myadd-in.xlam'!macro1

 

[JenniferMurphy]

You can do it, but you need to specify the full path & macro name like
'C:\Users\username\AppData\Roaming\Microsoft\AddIns\myadd-in.xlam'!macro1

I assume you meant single quotes around the path, then "!" and the macroname, right?

I put this simple macro in a module in my add-in

Sub AddinControlMacro()
MsgBox "This code is in an axd-in module!", , "AddinCongtrolMacro"
End Sub

Then I added a button control to a workbook sheet (.xlsx), right-clicked the control, and selected "Assign macro...". When the Assign Macro dialog came up, I pasted this string into the Macro name: field.

'C:\Users\JM\AppData\Roaming\Microsoft\AddIns\My Add-Ins.xlam'!AddinControlMacro

The Macros in: field has All Open Workbooks.

When I click on the control, I get this error message:

If I reopen the Assign Macro dialog, I see that the entire path has been deleted, leaving only the macro name.

Also, why am I not able to move the cursor within the Macro Name field. Backspace works, but not the left/right arrow keys.

 

[Fluff]

I assume you meant single quotes around the path, then "!" and the macroname, right?

That's right.

If I reopen the Assign Macro dialog, I see that the entire path has been deleted, leaving only the macro name.

I get the same thing, but I've found that if you don't enter the full path it won't work.

I can only assume you get the error because of your macro security settings.

 

[JenniferMurphy]

Here are what I assume are the macro security settings:

I pasted into the Macro name field of the Assign Macro dialog what I posted above:

'C:\Users\JM\AppData\Roaming\Microsoft\AddIns\My Add-Ins.xlam'!AddinControlMacro

When I click on the Button control, I get that same error message. When I reopen the Assign Macro dialog, I see:

What more can I do?

 

[Gokhan Aycan]

If I reopen the Assign Macro dialog, I see that the entire path has been deleted, leaving only the macro name.

Also, why am I not able to move the cursor within the Macro Name field. Backspace works, but not the left/right arrow keys.

Same behaviour on both accounts here as well.

It is working for me unless I am doing something wrong in the first place (first time using an add-in). Checked both with and without a space in filename.

Only difference in settings is "Trust access to the VBA project object model" is unchecked.



Maybe repair Office installation?

 

[Fluff]

Not sure why you are getting the warning message as your macro security settings are fine.

 

[JenniferMurphy]

You can do it, but you need to specify the full path & macro name like
'C:\Users\username\AppData\Roaming\Microsoft\AddIns\myadd-in.xlam'!macro1

Not sure why this wasn't working for me bdefore, but it's working now. Thanks