autorun macro when opening Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1694595%22%20slang%3D%22en-US%22%3Eautorun%20macro%20when%20opening%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1694595%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20complete%20an%20open%20event%20code%3A%26nbsp%3B%20to%20automatically%20run%20another%20macro%20when%20opening%20Excel.%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20the%20code%20for%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1694595%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1695484%22%20slang%3D%22en-US%22%3ERe%3A%20autorun%20macro%20when%20opening%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1695484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F801978%22%20target%3D%22_blank%22%3E%40deb_h%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20you%20said%20when%26nbsp%3B%3CSTRONG%3EExcel%3C%2FSTRONG%3E%20opens.%20To%20do%20that%20you%20must%20first%20learn%20how%20to%20run%20a%20macro%20when%20a%20workbook%20opens.%20So%20let%20us%20tackle%20that%20first%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%203%20options%20to%20'auto%20run'%20a%20macro%20when%20a%20workbook%20opens%3A%3C%2FP%3E%3COL%3E%3CLI%3EAuto_Open()%20-%20Create%20a%20routine%20named%20Auto_Open%20in%20a%20regular%20module.%20This%20is%20the%20most%20common%20method%20but%20these%20least%20robust%20(rarely%20doesn't%20run).%26nbsp%3B%3C%2FLI%3E%3CLI%3EWorkbook_Open()%20-%20Use%20the%20Workbook%20Open%20event%20in%20ThisWorkbook.%20This%20is%20the%20second%20most%20common%20and%20a%20bit%20more%20robust%20(See%20below)%3C%2FLI%3E%3CLI%3ERibbon%20OnLoad%20event%20-%20This%20is%20the%20hardest%20to%20implement%20but%20works%20every%20time.%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3CP%3EFor%20this%20we%20will%20use%20the%20ThisWorkbook%20Workbook_Open%20event.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnder%20your%20workbook%20click%20ThisWorkbook%20in%20Project%20Explorer.%20Then%20select%20Workbook%20in%20code%20window%20then%20select%20Open%20in%20dropdown.%26nbsp%3B%20Add%20code%20to%20Workbook_Open()%20sub%20which%20can%20be%20the%20code%20you%20want%20to%20run%20or%20a%20call%20to%20another%20routine%20to%20run%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22BXLThisWorkbook.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F220398iAB396385B3C2A228%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22BXLThisWorkbook.png%22%20alt%3D%22BXLThisWorkbook.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20now%20you%20have%20code%20that%20will%20run%20when%20this%20workbook%20opens.%20To%20make%20it%20run%20everytime%20Excel%20opens%20we%20have%202%20options%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ESave%20the%20workbook%20as%20an%20Add-In%20and%20make%20sure%20the%20add-in%20is%20loaded%20when%20we%20exit%20Excel.%20The%20next%20time%20Excel%20opens%2C%20the%20add-in%20loads%20and%20fires%20its%20workbook_open%20event.%26nbsp%3B%3C%2FLI%3E%3CLI%3ESave%20the%20workbook%20as%3A%3CBR%20%2F%3E%3CSPAN%3EC%3A%5CUsers%5C%3CYOUR%20user%3D%22%22%20id%3D%22%22%3E%5CAppData%5CRoaming%5CMicrosoft%5CExcel%5CXLSTART%5Cpersonal.xlsm%3C%2FYOUR%3E%3C%2FSPAN%3E%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3EI%20recommend%20option%202.%20Here%20are%20resources%20to%20explain%20it%20in%20more%20detail%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fanalysistabs.com%2Fexcel-vba%2Frun-macro-automatically-opening-workbook%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fanalysistabs.com%2Fexcel-vba%2Frun-macro-automatically-opening-workbook%2F%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Fpersonal.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Fpersonal.htm%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Trying to complete an open event code:  to automatically run another macro when opening Excel.

Does anyone know the code for this?

1 Reply
Highlighted

@deb_h 

 

So you said when Excel opens. To do that you must first learn how to run a macro when a workbook opens. So let us tackle that first

 

There are 3 options to 'auto run' a macro when a workbook opens:

  1. Auto_Open() - Create a routine named Auto_Open in a regular module. This is the most common method but the least robust (rarely doesn't run). 
  2. Workbook_Open() - Use the Workbook Open event in ThisWorkbook. This is the second most common and a bit more robust (See below)
  3. Ribbon OnLoad callback- This is the hardest to implement but works every time (most robust)

 

For this discussion we will use the ThisWorkbook Workbook_Open event.

 

Under your workbook click ThisWorkbook in Project Explorer. Then select Workbook in code window then select Open in dropdown.  Add code to Workbook_Open() sub which can be the code you want to run or a call to another routine to run

BXLThisWorkbook.png

 

So now you have code that will run when this workbook opens. To make it run every time Excel opens we have 2 options:

 

  1. Save the workbook as an Add-In and make sure the add-in is loaded when we exit Excel. The next time Excel opens, the add-in loads and fires its workbook_open event. 
  2. Save the workbook as:
    C:\Users\<your user ID>\AppData\Roaming\Microsoft\Excel\XLSTART\personal.xlsm

For this discussion I recommend option 2. Here are resources to explain it in more detail:

https://analysistabs.com/excel-vba/run-macro-automatically-opening-workbook/

https://www.rondebruin.nl/win/personal.htm