+ Reply to Thread
Results 1 to 5 of 5

Change sheet macro name in a new copied workbook.

  1. #1
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Change sheet macro name in a new copied workbook.

    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
    Last edited by Richard Buttrey; 06-27-2009 at 03:04 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Change sheet macro name in a new copied workbook.

    I'll look at that, but why not use ActiveX buttons?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Change sheet macro name in a new copied workbook.

    This worked for me, Richard:
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Change sheet macro name in a new copied workbook.

    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,

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Change sheet macro name in a new copied workbook.

    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.
    Last edited by shg; 06-27-2009 at 03:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1