Excel Add-In with Command Button Controls

[Mark Dev]

All,

Using Excel XP, I have created an Add-In which contains a worksheet. When
the add-in is loaded, the worksheet is populated with data and then a copied
to the client workbook. The worksheet also contains a command button.

Two issues:

1. If I use the Control Box command button, it is not copied to the client
worksheet along with the rest of the form. The click event code appears in
the general section of the form, but the control itself is not copied.

2. If I use the Forms command button, it is copied correctly to the client,
but the "Assign Macro" value still contains a reference to the Add-In (i.e.
"MyAddIn!CommandButtonAction"). If the user saves the client worksheet and
then happens to re-open it when the Add-In is not loaded, clicking the
button causes an immediate run-time error, that I am unable to trap.

I'd like to have the command button refer to a procedure in the copied
client worksheet so I'll have an opportunity to check for the existence of
the Add-In and raise an appropriate error if it is not loaded.

I have no preference as to which type of command button that I use, as long
as it performs correctly.

Any suggestions will be most appreciated.

Thanks,

Mark

 

[Jim Cone]

Mark,

If I am following your explanation correctly,
you and your client would probably be happier if the
add-in created a toolbar button upon install and
deleted the button when it was uninstalled.

The ThisWorkbook module contains AddinInstall
and AddinUninstall events to handle the code to
do the button creation/deletion.

Regards,
Jim Cone
San Francisco, USA

 

[Mark Dev]

Jim,

Thanks for your response. Appreciate you taking the time to help.

However, it is not a toolbar button that I'm referring to. It is a command
button on the worksheet itself. The user never sees the worksheets
contained in the Add-In, but I create a copy of them for the Add-In client
workbook. When I use the Control Box command button (which functions more
like a standard VB command button; with a Click event, etc.) and copy the
worksheet from the Add-In to the user's workbook, a perfect copy of the
worksheet is created... minus the command button. It just doesn't copy
over.

If I use the command button from the Forms toolbox, the command button is
copied just fine, but the "Assign Macro" property of the command button
still refers to the subroutine in the Add-In. That's fine as long as the
Add-In is loaded. But if the user saves this worksheet and then happens to
re-open it when the Add-In is not loaded, the code blows up when the command
button is clicked. I'd at least like to be able to trap the error and tell
him "You have to load the Add-In first!", but there is no opportunity to
trap the error; it just blows up immediately before ever reaching my error
handling code.

Thanks again,

Mark

 

[Mike Fogleman]

Jim is pointing out that you will have better success with this procedure if
you scrap the CommandButton idea completely and use the ToolBar Button
method to fire your macros. Think outside the copied sheet.
Mike F

 

[Mark Dev]

Mike/Jim,

Understood. Sorry for being slow to catch on. I'd like to give that a
shot, but it raises other issues, like context sensitivity. If it is
created as a toolbar button, it should only be enabled when that specific
"client" worksheet is active, creating another situation where the copied
(client) worksheet will have to communicate to the add-in. Which requires
code in the client worksheet... which will blow up if the Add-In is not
loaded. But I will give it a shot and see how it works out.

Thanks again for your help. It is greatly appreciated.