Got any Excel Questions? Free Excel Help
Auto Run Excel Macros Upon Open.
Excel has the ability to automatically run/execute any Excel macro
code of our choosing when opening an Excel Workbook. This of course is providing
that the user has chosen to enable macros! In the past (pre Excel 97) one
would name a macro Auto_Open and Excel would automatically run/execute any
code within this procedure. This method, by the way, is still workable in Excel
versions since 97 for backward compatibility.
In Excel 97 Microsoft introduced what are called Events , one of these is the Workbook_Open Event. However, unlike the old Auto_Open macro, the Workbook_Open event is a procedure of the Workbook Object and as such, the Workbook_Open procedure MUST reside in the private module of the Workbook Object (ThisWorkbook). The Auto_Open macro on the other hand, must reside in a standard public module.
Accessing the Private Module of the Workbook Object (ThisWorkbook)
There are at least 2 ways to gain access to the private module of the Workbook Object (ThisWorkbook)
1) While in Excel proper, right click on the Excel icon, top left next to File and choose View Code.
2) While in the VBE double click the Module called ThisWorkbook, seen in the Project Explorer (View>Project Explorer (Ctrl+R))
Once here, you can select "Workbook" from the Object drop down list, located in the top left of the module pane. After you have selected "Workbook" Excel will default to;
Private Sub Workbook_Open()
Note, in Excel 97 accessing ThisWorkbook would automatically add the Workbook_Open event.
How to use the Workbook_Open Event
There are basically 2 ways we can have Excel VBA macro code run/execute via this Workbook_Open event. See the 2 simple examples below;
Private Sub Workbook_Open() MsgBox "Hi, thanks for opening me", vbInformation, "Ozgrid.com" End Sub Private Sub Workbook_Open() Run "MyMacro" End Sub
In the second case, "MyMacro" must be the name of the
procedure in any standard public module (Insert>Module).
If you wish to run/execute code that also resides in the same private module you cannot use the Run statement, only the procedure name. E.g.
Private Sub Workbook_Open() MyMacro End Sub Private Sub MyMacro() MsgBox "Hi, thanks for opening me", vbInformation, "Ozgrid.com" End Sub
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to email@example.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates