Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Long Beach, California, USA
    Posts
    1,912
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As moves Macro... and VBA 400 error. (2003)

    Hmmm...

    1) Create a spread sheet with a Macro.
    2) Create a button on the button bar and assign that Macro to the button.
    3) Save and close the spread sheet file.
    4) Reopen the flie and the Macro is still assigned to that button.
    5) Make some modifications on the sheet and "save as" and close the sheet as a new file.
    6) Reopen the original file and the Macro has now been moved to the new file -- not the original file where the Macro was created! You can verify that by looking at the Macro assignment under Toolbar | Customize | Modify Selection | Assign Macro.
    7) Try to run the Macro and you get slapped with a VBA "400" error.

    Any thoughts?? I want the button to be STAY assigned to Macro created in the original file. Why does the assignment move? If I try to reassign it, and save the file -- this works only one time. The next time I use Save As... the assignment moves again!

    Thanks for any help.

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

    Re: Save As moves Macro... and VBA 400 error. (2003)

    You can use the VBA SaveCopyAs method:
    - Create a workbook with a macro.
    - Create a custom toolbar button and assign the macro to it.
    - Save the workbook.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type <code>ActiveWorkbook.SaveCopyAs "Newname.xls"</code> then press Enter.
    - Switch back to Excel.
    - Close the original workbook and open the new one.
    - You'll see that the toolbar button still refers to the original workbook.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Long Beach, California, USA
    Posts
    1,912
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As moves Macro... and VBA 400 error. (2003)

    That works with the following caveats:

    1) I have to always use ActiveWorkbook.SaveCopyAs to save the new sheet -- I cannot simply hit SaveAs... (Is there a way to get this as a button on the toolbar???) The number of steps involved in saving each new sheet is a little large.

    2) Even when I do this, if I reopen the original file and run the Macro -- I still get the 400 error!!!
    _______________

    I never had this problem in the past. I don't know if it is an Excel 2003 problem or if it came after some recent office udate. But I have been doing this process for 15 years and it was not always this way.

    In brief, I have a partially preformated Excel spreadsheet that I use to do my weekly billing. The file is named 0-blank.xls (so that is always stays at the top). At the end of each week I open the sheet and paste in that week's billing information. To complete the final formating I have a button on my toolbar that points to a Macro that centers columns and makes the sizes of the column fit correctly. That is it.

    I have used this process for 15 years with various versions of Excel. Originally I swear the Macro stayed in 0-blank.xls. But somewhere along the way I ran into a problem where the Macro originally written in 0-blank.xls got reassigned to another sheet. I would have to use the "Hide sheet" process to avoid perpetually having to see the unneeded sheet.

    There must be some method to "use" 0-blank.xls as a template, but not have the Macro reassigned each time I save it as a different sheet. What if I refer the Macro to a third .xls file?? Can I make up a file that just holds the Macro -- and never SaveAs... on that file?? Will this eliminate my 400 error??

    Thanks.

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

    Re: Save As moves Macro... and VBA 400 error. (2003)

    If this is for your personal use, why not store the macro in your Personal.xls macro workbook instead of in 0-blank.xls. Macros in Personal.xls can be run from any workbook.

    See Legare Coleman's Personal.xls Tutorial (All) for details on how to create/use it.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Long Beach, California, USA
    Posts
    1,912
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As moves Macro... and VBA 400 error. (2003)

    Because I am too illiterate! Thanks, Hans. I'll give it a try. Sounds like a good idea.

Posting Permissions

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