none
Excel 2016. How to ensure Macro is always loaded/available. RRS feed

  • Question

  • Hello,

    We have a few users who need a particular macro available to them, always. 

    To initially make the macro available, I took the following action:

    1. In Excel, File | Options | Customize Ribbon | Checked Developer to make it available.
    2. Developer | Visual Basic.
    3. File | Import File.  Imported the macro which is named Macro.BAS. Close | Return to Excel.
    4. Now, under Macros, the required macro is listed.

    However, when Excel is closed and reopened, it is no longer available.  How do I set it up so that it is available.  An additional bonus would be to create a button that simply launches the macro.

    Thanks in advance.

    Regards,

    Rudy

    Tuesday, April 16, 2019 6:32 PM

Answers

All replies

  • Tuesday, April 16, 2019 7:36 PM
  • Hi,

    Just checking in to see if the information of Hans Vogelaar was helpful. Please let us know if you would like further assistance. 

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, April 17, 2019 6:57 AM
    Moderator
  • Hans & Emi,

    Thank you.  That link was helpful.  So, I now have a folder: C:\Users\%User%\AppData\Roaming\Microsoft\Excel\XLSTART, in which I have created a file, Personal.xlsb which has the macro saved.

    Everything is working as needed, with the macro being available always.  I was able to figure out how to create a button on the ribbon to launch the macro with a single click.

    Now, when Excel is launched, it opens a new spreadsheet with the name, Personal.xlsb.  If I open an Excel spreadsheet file [or shortcut], it opens the file I selected in addition to the separate Personal.xlsb.  So two files open.  Is this how it's supposed to work? 

    Thanks,

    Rudy

    Friday, April 19, 2019 11:15 PM
  • Hello Hans and Emi,

    Yes, with Hans' link, I am able to get the functionality that I need.  I do have some follow up questions.

    The current configuration is as follows:

    1. I have imported the macro, and saved the file as C:\Users\%User%\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xlsb.
    2. When I launch Excel, the spreadsheet opens with the name "Personal.xlsb", not with the default name of Book.xlsx. 
    3. When I open an existing spreadsheet, it opens it, but also opens Personal.xlsb. 

    Is this the behavior that I should expect now?  Is this normal?

    Thanks again for your help.

    Regards,

    Rudy

    Monday, April 22, 2019 4:18 PM
  • Start Excel.

    Make sure that Personal.xlsb is the active workbook.

    On the View tab of the ribbon, in the Window group, click Hide.

    Quit Excel.

    You'll be asked whether you want to save changes to Personal.xlsb. Click Yes.

    Next time you start Excel, Personal.xlsb will be loaded so that macros are available, but it will be hidden.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, April 22, 2019 6:20 PM
  • Hans - THANK YOU!!!

    Really appreciate your understanding of the issue, and clear direction to the resolution.

    Regards,

    Rudy

    Tuesday, April 23, 2019 4:00 PM