Excel add-ins fail to load

Content

Introduction

As you probably know, I offer a number of tools for your daily Excel work:

  • RefTreeAnalyser The best tool to dissect your formulas and find out where the data for a formula comes from
  • FlexFind a tool with which you can do search and replace in all of Excel's objects, not just cells
  • Name Manager The best tool to work with range names
  • Other free tools

There is a problem however.

I've been getting reports from people complaining that they can no longer use add-ins. In this article I describe the problem and a solution.

The problem

Even though an add-in has been "installed" into Excel using the Add-ins dialog its userinterface does not show. This is that dialog (you get there by going to File, Options, Add-ins tab and pressing the Go... button):

Add-ins dialog of Excel

As you can see, some add-ins are checked. But sometimes the user-interface of an add-in fails to display. In fact, the add-in file will not open in Excel at all, even if you try from Windows Explorer, or from File, Open from within Excel. Without any error message.

The cause

A recent Office Security update has caused any file downloaded from the internet (so this includes add-ins) to be marked as Blocked. Even if those files were in a zip container, they are marked as blocked. And Excel will simply not open them.

Solution

Unblock the files! Like so:

1. Copy the file from the zip file to any folder

2. Right-click the file and choose properties:

Select file properties

3. Click the Unblock button:

Click Unblock

Additional problems

Some users report that even though they can check an add-in in the add-ins dialog and that add-in then works, as soon as they close and reopen Excel, the add-in is no longer installed (and hence does not open).

This is probably due to a bug in Excel which causes the add-in entries in the registry to be wrong.

Make sure you close Excel first. Open the registry editor and navigate to this location: Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options

OPEN entries in Excels registry settings tree

In that key, most of Excel's settings are listed. Find the ones that begin with the word "OPEN" (without quotes). Look closely, these MUST be numbered in order: OPEN, OPEN1, OPEN2, OPEN3, ...

If there is a gap, make sure you close that gap by renaming them. After renaming them, you should be able to add the add-in as usual.

Other resources

If you have a folder full of blocked files, this page describes a way to unblock all of them using Powershell.

And a great article by fellow-MVP Jon Acampora: Excel Add-in Ribbon Disappears After Installation.

This article describes the mechanics of the problem very well: http://www.howtogeek.com/70012/what-causes-the-file-downloaded-from-the-internet-warning-and-how-can-i-easily-remove-it/


Comments

Showing last 8 comments of 63 in total (Show All Comments):

 


Comment by: Paulo Lamy Roque (1-2-2022 10:33:00) deeplink to this comment

Hello, it's the samradapps date picker.


Comment by: Jan Karel Pieterse (1-2-2022 10:42:00) deeplink to this comment

Hi Paulo,

I'm afraid you'll have to contact Sam about that, through his website: http://samradapps.com/about


Comment by: Abdul (17-5-2022 09:24:00) deeplink to this comment

hello dear i'm juste a beginnger in excel but i want to learn the code made by sam when i go to visual Basic to see the code of sam, a texte box pop in and the massage " VBAProject password" ask me to enter the passwoed can i ask you the     password please? I want to understand the code completly please it for a project to validate my graduation this year


Comment by: Jan Karel Pieterse (17-5-2022 09:57:00) deeplink to this comment

Hi Abdul,

By Sam I presume you mean Sam Radakovics from http://samradapps.com/datepicker
Sam's not me so you'll have to ask Sam I'm afraid :-)


Comment by: Abdul (17-5-2022 12:08:00) deeplink to this comment

Thnk's a lot for you quick reply Jan Karel Pieterse Ok i will do that but i can't see contact us or contact me to ask directly to Sam Radakovics the passeword can you explain me How to contacte Sam please ?


Comment by: Jan Karel Pieterse (17-5-2022 13:59:00) deeplink to this comment

Hi Abdul,

I am not at liberty to share Sam's email address I'm afraid. But maybe you can find his profile on Linked-In and try to contact him there?


Comment by: Hans Knudsen (27-5-2022 11:49:00) deeplink to this comment

Every time I (in Excel 365) press Insert, Get Add-ins and in the Office Add-ins dialog try to add "Advanced Formula Environment" add in I get (after a long time) the message "Error loading add-ins

What to do?


Comment by: Jan Karel Pieterse (27-5-2022 14:07:00) deeplink to this comment

Hi Hans,

I'm not sure, I think you better ask that question here:
https://techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].