Buttons on worksheet opies always point to macros in orignal worksheet. 1

[MigrantFirmWorker]

I have an Excel worksheet with a macro embedded in it (not in a module). This macro is invoked by a button on the worksheet. When I copy the worksheet, the macro and button are copied with it, as intended. Unfortunately, the button on the copied sheet points to the macro in the original sheet, not the copy.

My objective is for this worksheet to be portable and copyable, where the button always points to the macro in its associated worksheet. Any suggestions?

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[SkipVought]

Chris,

Why don't you look at the button call reference?

Change the sheet reference to ActiveSheet in the original and see what happens.

 

[MigrantFirmWorker]

No luck. It seems to want a hard reference to a worksheet (workbookName.xlsm!Sheet1.macroName), or just the macro name from a resident module.


Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[SkipVought]

But you see what's happening, right?

So part of your coded copy process is to change the reference to the reality of the new state.

 

[MigrantFirmWorker]

I may be missing something here. I'm not using any code for the copy operation. I'm merely trying to set up the worksheet such that a user can move or copy it within, or between, workbooks without having to reassign the macro to the button.

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[SkipVought]

Well, from what you've observed as a programmer in the past 3 hours, what do you think your options are?

 

[MigrantFirmWorker]

So far, my options haven't changed from when this first came up.
1) I can have the user manually reassign the button on the new worksheet.
2) I can place the macro in a module and force the user to copy it along with the worksheet to any new workbooks.
3) I can add another macro to automate the copy operation.

1 & 2 will generate too many phone calls.
3 will likely invoke the issues in 1 & 2.

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.

 

[combo]

Use activex (MSForms) button instead and put the code in the "Click" event procedure.

combo

 

[MigrantFirmWorker]

Perfect! Thanx.

Chris

-------------------------------------------------------------
"Don't be deceived. We're all temporary employees.