VBA – Run a macro when Excel starts

Associated Files Download Links


Do you need to run a macro when Excel starts? You have two options:

1. Create a Workbook_Open() sub within ‘ThisWorkbook’.

2. Place a Auto_Open() sub within any module.

Workbook_Open Event

Create a sub titled ‘Workbook_Open’ within ‘ThisWorkbook’

Auto_Open

Using the second method: Simply create a subroutine called Auto_Open and place code in it, or call another sub from there. Automatically your code runs when Excel starts.

Create and Name New Worksheet Everytime Excel Opens

The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesn’t already exist – to allow for the possibility of it being opened more than once a day.

This code makes use of the Workbook Open Event and must be placed in the workbook module under the “Open work Book” event. The function Sheet_Exist must be placed in a module and this checks whether or not the sheet exists:

To download the .XLSM file for this tutorial, click here

Set the Default Sheet When Workbook Opens

Do you want to make sure a sheet always shows first when a workbook opens? For instance when you open a workbook sheet3 is always the active sheet. Here’s how.

You can refer to a sheet from VBA by it’s program name (ie Sheet3) or by it’s tab name(ie JanData). It is best to use the program name, becuase if the tab name changes, your VBA code that refers to a tab name will no longer work. However if you use the program name a user can change the tab name multiple times and your macro still works.

To make sure a certain sheet is always activated when a workbook opens, just place sheet.activate code in the workbook_open sub. This is an example that activates sheet3 by using the program name everytime a workbook opens.

And this does it by using the tab name:

Sidenote: You must save and restart excel for this to work.
Sidenote: This only works if macros are enabled.
Sidenote: Put this code in the code window for the ThisWorkbook object in the VBE.

Load Form Every Time Workbook Opens

If you would like to load a form or run some VBA code when you open an excel workbook, place your code in the Thisworkbook code window and in the Workbook_Open sub.

From your spreadsheet:
1. Press ALT and F11 to open the VB editor
2. Double-click the word ThisWorkbook to open the code window
3. Type the following code in the ThisWorkbook code window

Sidenote: Replace Userform1 with your form name

4. Close Excel and re-open.

load-form-when-excel-starts

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues: