Add macros on Open

Posted on 2016-12-02
Medium Priority
Last Modified: 2016-12-04

I have a list of macros in an excel file (also have them in a .bat file)
When I open excel, I want these macros to open as well so I can use them.

I know I can add the excel file to the C:\Users\<your username>\AppData\Roaming\Microsoft\Excel\XLSTART. but that opens the file with the list of macros.  
I do not want the file to open.

How can I have access to these macros "on open" of excel without seeing the file or is there something similar in excel like MS WORD has in the Normal template?
Question by:pdvsa
  • 3
  • 3
  • 2
  • +1
LVL 54

Expert Comment

ID: 41909903

One way to do it is to find the book.xltx file add the macros save it as xltm then whenever xl starts you will have the macros in the "normal" template
or you use the workbook_open event of your file to hide it

 Private Sub Workbook_Open()
Me.Windows(1).Visible = False
End Sub

Open in new window

LVL 35

Accepted Solution

Rob Henson earned 2000 total points
ID: 41909949
Put the macros into the Personal.xlsb file which is stored in the XLSTART directory. Once you have the macros in the file, use the View ribbon to Hide the file. Closing Excel will then ask if you want to Save your Personal file and you need to say yes. When opening Excel the personal file will open but will be hidden.

If you can't find the Personal.xlsb it probably hasn't been created. Use the Macro recorder to record a very simple macro. In the initial popup for the recorder use the "Store macro in" drop-down to choose Personal Macro Workbook. Simple macro can be as simple Start recording, select a cell, stop recording. Once the Personal has been created this simple macro can be deleted.

Rob H

Author Closing Comment

ID: 41909953
Ahh yes i seem to remember this.  I do also remember that I have not setup the personal excel file.  I will do this.

Very nice.

Thank you!
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 23

Expert Comment

by:Roy Cox
ID: 41910385
My personal preference to the PERSONAL workbook is create an Excel addin. You have more control over this and can easily transfer it to another computer or user

Author Comment

ID: 41911883
Rob, I have created the macro and stored it in the personal.xlsb
However, I still do not see the personal.xlsb in the following path:

I did verify that the macro was created and is stored in the "personal.xlsb"
I also unhid the personal.xlsb

Do you happen to know why the file doesn't appear in the XLSTART?
I am on a work computer but I can access the path.
LVL 23

Expert Comment

by:Roy Cox
ID: 41911902
The Personal workbook is hidden by default to prevent accidental deletion or changes.

In the View Tab of the Ribbon you will see the Windows group. In that group is a button labelled unhide, click it and a dialog listing hidden workbooks should be displayed. Select personal.xlsb and click OK.


You shouldn't need to do this except to edit the macro. When finished hide the workbook again

Author Comment

ID: 41911909
HI Roy, thanks for the response.   I am still a little puzzled because I did unhide the file but its not showing.  Please see screenshot of the personal.xlsb file showing that it is unhidden (its greyed out).

LVL 23

Expert Comment

by:Roy Cox
ID: 41911918
Looking at the screen shot it appears that Personal.xlsb is actually unhidden. What happens when you look in the VB EDitor, can you see the code.

Click File in that workbook's Ribbon and on the left click "Open file location", see which folder is opened
LVL 35

Expert Comment

by:Rob Henson
ID: 41911999
In Explorer go to Folder options and one of the options will be to hide system files. Untick that and the file should appear. Don't know why you would need to do that though???

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question