Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Automatically Loading Add-ins.
Michael asked if there is a way to selectively load add-ins for specific worksheets. There is a way to do this, but it involves the use of macros attached to the Workbook module for the specific worksheets. Follow these general steps:
Figure 1. Double-click the ThisWorkbook object in the Project Explorer.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Add-In Name").Installed = False
End Sub
Private Sub Workbook_Open()
AddIns("Add-In Name").Installed = True
End Sub
If you are not sure of the correct name for a particular add-in (see step 5), you can use the macro recorder to record the process of activating an add-in. That will show you the exact name you should use in the above macros.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11788) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Automatically Loading Add-ins.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel normally refers to columns as A, B, C, etc. It also has a referencing format that allows columns to be referred to ...
Discover MoreExcel allows you to specify where it stores various files used by the program. One location you can specify is where ...
Discover MoreSome people like zero values displayed; others do not. Excel allows you to easily turn the display on or off for a single ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-03-02 11:04:31
David Gray
The problem with any such approach is that macro code in a template is copied into the new workbook, marking it as macro enabled. Even if you save it as a plain workbook, you must first deal with the embedded macro prompt.
2018-03-01 08:56:57
Talak
there are several best ways to achieve that than type code every time you create a new file (assuming you always create Workbooks manually).
First option (cleanest)
Every time you create a new Document in Excel (same with other Office documents), the application loads the default model.
You can edit this model and load every add-in you want. Every time a new document is created, the custom default add-ins will be loaded as well.
First option (less clean but supports automation and better adaptivness)
You can write code in VBA using code.
In other terms, you can make a code every time you open the default document which writes some code lines likes the one you wrote automatically on any new document or with the "saveas" option (note that XLSX workbooks will display an error message when trying to save macros).
As well, you can also change the DLLs or addins you want to load when the workbook is created by automation.
For example, you can have different addins in automated workbooks and manually created ones.
2017-09-06 14:25:21
David Gray
I've thought about this approach off and on for many years. My main issue with it is that it marks the workbook as Macro Enabled, and it must be saved as a .XLSM file. Since there's a good chance that the code that I want to use went into an add-in to avoid this issue, I am reluctant to implement it in the real world. There are just too many security issues with making everything a macro-enabled workbook.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments