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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Save As moves Macro… and VBA 400 error. (2003)

    • This topic has 4 replies, 2 voices, and was last updated 18 years ago.
    Author
    Topic
    #429804

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1002040

      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 ActiveWorkbook.SaveCopyAs "Newname.xls" 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.

      • #1002258

        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.

    Viewing 0 reply threads
    Reply To: Save As moves Macro… and VBA 400 error. (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: