Change sheet macro name in a new copied workbook.

[Richard Buttrey]

Hi,

I have a workbook, 'MyWb.xls' and a sheet with buttons to which I attach some sheet macros.
When I assign a macro to a button I pick the appropriate reference, e.g. Sheet2.MyMacro, and this gets recorded by default as MyWb.xls!Sheet2.MyMacro

When I create a new single sheet workbook with the instruction ActiveSheet.Copy, I end up with a new workbook, say 'Book2' along with the buttons as expected. However the macros attached to the buttons still refer to the MyWB.xls file and not the new 'Book2.xls'.

As part of the process of creating the new book I've tried redefining the macro with the instruction

Please Login or Register  to view this content.

but this doesn't change anything and I'm left with the reference back to the MyWb.xls macro, which of course is no use when the new book gets distributed.

Can anyone suggest a solution?
I've looked at putting the sheet macros in a procedure at the Module level, and then first exporting the module as a .bas file and re-importing it to the new book, but this seems unnecessarily complicated, and in any case if the Application does not have the Macro Security set to 'Trust Access to the Visual Basic Project' this won't work, and of course this setting can't be set programatically.

All ideas gratefully received,

Regards

[shg]

I'll look at that, but why not use ActiveX buttons?

[shg]

This worked for me, Richard:

Please Login or Register  to view this content.

[Richard Buttrey]

Excellent!

Thanks shg.

It didn't work first time for some reason, but when I left out the '.Name' and modified it slightly to

Please Login or Register  to view this content.

it works just fine. Is this perhaps because it's a Sheet level macro and not a Module level macro?

Obviously the .Parent.Name is the key. I'd tried something similar using the new workbook name instead, but this didn't work. There must be some subtle difference between using a variable containing a workbook name and the .Parent.Name. Interesting.

Anyway, once again many thanks - and of course for the extremely quick response.

Kind regards,

[shg]

You're welcome, Richard. It worked for me exactly as originally written, though -- I didn't want to hard-code the sheet name.

EDIT: I see why -- it should be

Please Login or Register  to view this content.

For anyone who wonders, this will NOT work:

Please Login or Register  to view this content.

... because here, ActiveSheet refers to the worksheet before it was copied, and the reference remains after the copy, even though it is no longer the active sheet.