Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigned macro gets lost (ExcelXP)

    I'm having trouble in releasing a workbook to a client; specifically the macro assignation seems to be hopelessly hard-wired.

    I'd like to know how to export a macro-toolbar that is transportable.

    1) I create a new workbook Alpha.XLS (ATTACHED)
    2) I create a Public macro AlphaM (whose sole function is to pop up a MsgBox)
    3) I create a toolbar AlphaT, and I drag a menu Item to that toolbar.
    4) I assign my macro AlphaM to that menu item on toolbar AlphaT.
    5) I have a choice of macros in (a) All open workbooks ([img]/forums/images/smilies/cool.gif[/img] This workbook or Alpha.XLS.; the first time around I select "This Workbook" because I have a vague idea that I'd like this toolbar and macro to be visible only when the user has loaded this workbook, and not at other times.
    6) The macro executes perfectly from the toolbar AlphaT.
    7) I exit and save the workbook,
    8) I rename it (Windows Explorer) to be beta.XLS
    9) I load and run the toolbar menu item and receive "Alpha.XLS could not be found"

    Interrogation of the assigned macro in Beta.XLS shows that it is assigned as [/b]'C:TempAlpha.XLS'!AlphaM[/b]

    I reason that the hard-coded path is a problem, delete it, run again, failure. The hard-coded path has re-appeared.

    I try "All Open Workbooks" and "This workbook" in a similar manner with no real success.

    If I use File, saveAs within Excel (saveas "Gamma.XLS"), exit and save, and reload as Gamma.XLS, the assignation has changed to be 'C:tempGamma.xls'!AlphaM, and the macro runs well.


    I have come to the conclusion that renaming a worksheet outside of Excel can cause havoc with macros assigned to toolbars, but this seems wrong to me.

    Is my thinking flawed?

    Ought I to be able to create a workbook with toolbar and macros and have it work even if it is renamed, or for that matter, moved to a different folder? Such as that of the client site.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Assigned macro gets lost (ExcelXP)

    Yes, the path of the macros assigned to custom toolbar buttons is hard-coded. See Pieterse's star post <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post> for a method to deal with this.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigned macro gets lost (ExcelXP)

    Hans, thanks for confirming the news about hard-coded paths. After a couple of false starts (me slowly going insane ....), this seems to work perfectly.

    I implemented Jan's code, saved and retested the worksheet. Then I exited Excel, renamed the sheet as Delta.XLS and reloaded. It works!

    Thanks again.

Posting Permissions

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