Opening a workbook runs a macro automatically
This article explains how to set up a workbook so a macro is run every time you open the workbook. Excel Event code makes this possible and it is easier than you think.
Event code can run a macro based on a drop-down list, event code is VBA code stored in a worksheet module or workbook module. I will explain this later on in this article.
For example, you could have a macro that opens another workbook that you need, saving you time, making you happy, and feeling awesome.
What's on this page
1. Run a macro automatically when a specific workbook is opened
1.1 Create a macro
The following macro opens workbook Book1.xlsx in folder c:\temp
'Name macro Sub Macro1 'Open a given workbook Workbooks.Open ("c:\temp\Book1.xlsx") End Sub
1.2 Where to put the code?
Copy the macro code above and go to tab "Developer" on the ribbon. If it is missing search the internet for your Excel version and "Show developer tab".
Press with mouse on "Visual Basic" button to open the Visual Basic editor. Press with right mouse button on on your workbook in the Project Explorer window.
Press with mouse on "Insert" and then on "Module". This action adds a code module to your workbook.
Now paste the VBA code above to your code module.
Go back to Excel.
1.3 How to run macro
Go to tab "Developer" and press with left mouse button on the "Macro" button. The following dialog box appears:
Press with mouse on "Run" button and the workbook is opened.
1.4 Where to put event code?
Press Alt+F11 to open the VB Editor or go to tab "Developer" on the ribbon, press with left mouse button on "Visual Basic" button. Double press with left mouse button on "This Workbook", if you can't see it expand the list by press with left mouse button oning on the + sign.
Paste the following event code to the workbook module:
Private Sub Workbook_Open() Macro1 End Sub
Go back to Excel. Save the workbook with file extension *.xlsm, this is important.
Close workbook and open it again. The workbook Book1.xlsx in folder c:\temp is now automatically opened. (If it exists..)
2. Run a macro automatically when selecting a specific worksheet
This section describes how to run a macro if a specific worksheet is selected (activated). Each worksheet in a workbook has a corresponding worksheet module that you can easily access, however, put only event code in these modules.
Note, put regular macros in regular modules.
2.1 Worksheet event code
The following steps describe how to save event code to a specific worksheet.
- Press the right mouse button on the worksheet tab you want to edit, it is located at the left bottom of your Excel window.
- A pop-up menu appears, see the image above.
- Press with the left mouse button on "View Code" to open the corresponding worksheet module in Visual Basic Editor (VBE).
- Copy event code below.
- Paste to worksheet module.
'Event code that runs when the user press with left mouse button ons on a specific worksheet tab Private Sub Worksheet_Activate() 'Start macro named Macro2 Call Macro2 End Sub
2.2 Macro code
The following VBA code is a regular macro. it shows a message box containing "Hello world!". Here is how you store regular macros:
- Press Alt + F11 to open the Visual Basic Editor.
- Press the left mouse button on "Insert" on the top menu, see the image below.
- Press the left mouse button on "Module" to create a new module, they are located in the Modules folder shown in the image below.
- Copy code below.
- Paste to the module.
- Exit Visual Basic Editor.
Sub Macro2() MsgBox "Hello world!" End Sub
3. Run macro when a specific cell is selected
The image above demonstrates a macro that is run when a specific cell is selected on a specific worksheet. The example shown in the image above shows a message box containing text "Cell B2 is selected" when cell B2 is selected.
3.1 Worksheet code
The following steps describe how to save event code to a specific worksheet.
- Press the right mouse button on the worksheet tab you want to edit, it is located at the left bottom of your Excel window.
- A pop-up menu appears, see the image above.
- Press with the left mouse button on "View Code" to open the corresponding worksheet module in Visual Basic Editor (VBE).
- Copy event code below.
- Paste to worksheet module.
'Event code Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Interesect method returns a range object of a rectangular intersection of two or more cell ranges If Not Intersect(Target, Range("B2")) Is Nothing Then 'Start macro named Macro3 Call Macro3 End If End Sub
3.2 Macro code
The following VBA code is a regular macro. it shows a message box containing "Cell B2 is selected". Here is how you store regular macros:
- Press Alt + F11 to open the Visual Basic Editor.
- Press the left mouse button on "Insert" on the top menu, see the image below.
- Press the left mouse button on "Module" to create a new module, they are located in the Modules folder shown in the image below.
- Copy code below.
- Paste to the module.
- Exit Visual Basic Editor.
Sub Macro3() MsgBox "Cell B2 is selected" End Sub
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.