Automatically run a macro when opening a workbook

[connderosa]

I can't seem to figure this out, and now I'm afraid I may have done the dreaded:

"Important: VBA code cannot be undone, so make sure to test your code on a blank workbook, or a copy of an existing workbook. If the code doesn't do what you want, you can close the workbook without saving changes."

Re: https://support.microsoft.com/en-us/office/automatically-run-a-macro-when-opening-a-workbook-1e55959...

If anyone can help me, I'd appreciate it! BTW: I'm a fairly non-techie, so, please spell it out pretty much in layman's terms.

Thank you in advance!

[#2 · Accepted solution]

@connderosa you need to open the VBA window (ALT-F11) and then in the project explorer (CTRL-R if it isn't already open) you expand the 'microsoft excel objects' folder and double click the "ThisWorkbook".

You should then get a blank page as shown in the image below and click the left drop down and select 'Workbook'.  This should automatically insert a 

Private Sub Workbook_Open()

End Sub

Then put the code you want to run between those 2 lines

and in the end make sure you save it as a .xlsm or .xlsb file type

hmmm... I should have opened that link first as it says the same thing but better. lol.

so maybe you need to explain better what your question/need is

[#3]

Hey @mtarler! Do you know if it is possible to reference a specific code from another sheet within the workbook from the Private Sub Workbook call out? or does it have to have the full code listed on the ThisWorkbook page? I would like to run a Notification Email code that is currently on another sheet every time the workbook is opened but am running into a reference or call out issue when I try to run it through the ThisWorkbook page. Put simply, I don't think Excel knows where I want it to look to successfully run the notify macro. I've tried adjusted the reference in the code itself and am doing something wrong lol so I am hoping there is a way for me just to tell the ThisWorkbook code to run all macros on sheetX and call it good.

[#4]

no the code doesn't have to all be in that module. why is the other a Private Sub instead of a Public Sub? it is hard to see what exactly you are doing, you should share the workbook or a sample without any private info. If you can't post the workbook then you can host it in onedrive or the like and share a link. Also you should post this as a new question as this isn't directly related to the above post and you could get additional helpers chime in

[#5]

Ok.

[#6]

@mtarler 

Thank you.

[#7]

Suggestion: While you are testing your code, put a Stop statement as the first statement in your Workbook_Open event handler; save the workbook with that. The advantage to that: As soon as you open the workbook, execution will halt and the VBA editor will open. Press F5 to continue (or click the corresponding Continue button).

Also, if your code is sufficiently well-behaved (i.e., it does not rely on any module-scope of public-scope variables to be at their initial values), you do not have to close and reopen your workbook to test your code. Just place your cursor in the Workbook_Open event handler and press F5.

Remove the Stop statement when you are satisfied with your code.