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

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" wrote in message
...
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,

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


"Jim Cone" wrote in message
...
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" wrote in message
...
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 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" wrote in message
...
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


"Jim Cone" wrote in message
...
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" wrote in message
...
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