excel tip Archives

Macro Mondays – How To Run An Excel Macro Automatically When You Open Your Excel Workbook

March 30, 2020 by Barbara

Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2020 series. So, today I will show you a number of different ways to automatically run a VBA macro when you open your Excel workbook.

Method 1. Name Your Excel Macro Auto_Open

The simplest way to get a macro to run automatically is to actually just name the macro Auto_Open. This first method does have limitations which will be covered in its explanation below. The macro needs to be stored in the workbook you want to open should you choose to use this method. Follow the simple steps below:-

First, ensure the Developer Tab is enabled. If it is not you can follow my blog post here to enable it or watch my YouTube video here, or click on the link below.

Initially, you may get a warning about Macro Security. If this happens then you need to set the security level to enable all macros. Follow the steps below to do this.

Developer Tab
Code Group
Macro Security

Hit Enable All Macros (I suggest you turn this back to one of the Disable All Macros settings when you are finished working with macros to prevent any dangerous code running without notification).
Open the workbook you want to save the macro in
Hit Record Macro
Type the name Auto_Open in the Macro Name dialogue box
In the Store Macro list- you need to decide where you want the macro to be saved

Note. Store the macro in your Personal Macro Workbook if you want this macro to be available when you whenever you open Excel. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb.

If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Recording Your Excel VBA Macro.

Click Ok, then start to record your macro.
After you have finished recording your macro you can either hit Developer Tab | Code Group | Stop Recording or
Hit the Stop Recording icon on the left-hand side of the workbook Status Bar (this is the quickest method)

auto open excel macro excel

If you want this Auto_Open macro to be available to run automatically in another workbook then you need that workbook to be saved in the XLStart folder so that both workbooks are opened when Excel starts

If you need to find where your XLStart folder is then you can do so by (Excel 2013)

File
Options
Trust Centre
Trust Centre Settings
Trusted Locations – you will find it in here

Method 1 Limitations.

There are a few limitations when using the Auto_Open method.

If the workbook where Auto_Open is stored contains a VBA procedure in its Open event then that procedure will override the Auto_Open macro.
As Auto_Open runs before any other workbooks are opened, if you have recorded actions that you want Excel to perform on the default workbook or one opened from the XLStart folder then Auto_Open will not run when you restart Excel as the macro runs before the default and startup workbooks are opened.
Auto_Open will not run if you open a workbook programmatically.

If you do not want the Auto_Open macro to run when opening your Excel workbook, the just hold down the SHIFT key when you are starting Excel.

So, how do you get around these limitations if they occur?

Method 2. Create a VBA procedure for the Open Event

All you need to do to avoid these limitations is to create a VBA procedure for the Open Event –

Ensure the Developer Tab is available See link above
Open the workbook where you want to save your macro
Developer Tab – Code Group – Visual Basic
Project Explorer Window
Right-click This Workbook
View Code
From the Object List Select Workbook – an empty procedure is automatically created that looks like this

Add your code to the procedure in this example I have added today’s date to Cell A1. Very simple.

Switch back to Excel and save the workbook with the .xlsm extension (macro)
Close your workbook then reopen your procedure should run

These are two alternative ways to automatically run and Excel VBA Macro when your workbook opens.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday or Macro Monday Series Click The Links Below

Macro Mondays Blog Posts.

Formula Friday Blog Posts.

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

Macro Mondays -Open An Excel Workbook To A Specific Worksheet Tab

January 16, 2017 by Barbara

Hello Excellers, welcome to another blog post in my #MacroMonday series. Most Excel workbooks contain more than one worksheet, which can be helpful when you want to keep related data together. However, sometimes, you may only want to view data from a specific worksheet tab within a workbook. This blog post will show you how to open an Excel workbook to a particular worksheet tab. So, this tiny bit of VBA code will ALWAYS open at a specific worksheet or tab no matter what worksheet was active when the workbook was last closed and saved. If you want to see all of the blog posts in the Macro Mondays Series, you can click on the link below.

How To Excel At Excel – Macro Mondays Blog Posts.

Step 1. Prepare To Write The Macro.

First, we will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

Once in the Visual Basic Editor, it is time to write the code. Now, as this code is particular to this workbook, it needs to be saved in the workbook—no need to create a module as it uses the Workbook_Open method, that open the workbook to specific tab.

Step 2. Use The Workbook_Open Event.

Next, in the project window find the workbook name. Do this by clicking the + sign and to see all of the worksheets. Click ThisWorkbook and select the Open event in the Event drop down list. Excel will automatically insert the following lines of code for you. Any other lines of code sit in between these two lines.

Private Sub Workbook_Open()

End Sub

Step 3. Write The Two Lines Of Code To Open The Workbook To A Specific Tab.

There is really one simple step in this Macro. to name the sheet tab you want Excel to open the workbook to. In my example I have chosen “MM22”. Finally, time to test your code.

Excel Pivot Table Tip – Show Only The Top 10 Items In A Pivot Table

December 7, 2015 by Barbara

This tip is really useful if you have a Pivot Table with a large number of Row Items such a product ID’s and you only want to show your top selling products. You may have hundreds or even thousands of products but you only want to look at the data of your Top 10 selling products.

Instead of manually choosing the top 10 items… with a few clicks Excel does all of the hard work.

First we start with a standard Pivot Table- if you need more information on Pivot Table Basics you cam check out my Pivot Table Week posts right here.

Once your Pivot is created select the arrow to the right of the Row Header – in my example it is Product.

top 20 pivot table

Click Value Filters
Then Top 10
In the Top 10 Dialog Box you can fine tune the filter, but in this case I want the default of 10 items based on Total
To finish hit Ok

top 10 pivot table

Now your Excel Pivot Table will only display the top 10 produts based on their totals. This saves time sorting the Totals then manually clicking the top 10.

top 10 pivot table.1

More Excel Tips

[amazonjs asin=”0789748754″ locale=”US” tmpl=”Small” title=”Excel 2013 Pivot Table Data Crunching (MrExcel Library)”]

[amazonjs asin=”1932802444″ locale=”US” tmpl=”Small” title=”Learn Excel 2007 through Excel 2010 From MrExcel: Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis and Much More – 512 Excel Mysteries Solved”]

[amazonjs asin=”1118842243″ locale=”US” tmpl=”Small” title=”Excel Dashboards and Reports For Dummies”]

Formula Friday- My formula is not working- top reasons your formula is showing in Excel not the results.

July 17, 2015 by Barbara

Happy Friday Exellers . You may have already come across this problem, and if you have not, you probably will at some point when using Excel. The mysterious case of your formula is showing in the cell not the result of the formula looking like this.

Whilst it is really frustrating there are a number of reasons this can happen, and usually I just work my my way through them to resolve the problem.

You have show Formulas selected. You have have in error or accidentally hit CTRL+’ (the back quote symbol), or Formulas Tab, Formula Auditing. To resolve this just hit CTRL+’ again.

You may have typed ” or have some symbol in front of the = sign. Excel treats the cell contents as text so it will not evaluate the formula and just displays the cell contents.

Your cell is formatted as Text. If this is the case then Excel will treat the formula as text instead of evaluating the formula and generating the result. This is really easy to fix. Just select the cell and reset the formatting to General, or you can hit F2 and Enter to change the formatting to General.

This is not such a common reason for formulas showing rather than the results, but if users have not obviously got the Show Formulas options selected, then this reason is usually the problem when I am asked about it.

See More Excel News and Resources

Sign up to my FREE newsletter for 3 Excel Tips every 15 days and get my FREE Excel tips EBook of my top 50 Excel Tips. Just fill in the form for instant download.

Other places you can can find me for more Excel tips.

Macro Mondays – How To Run An Excel Macro Automatically When You Open Your Excel Workbook