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 67 in total (Show All Comments):

 


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


Comment by: Joakim Nielsen (29-11-2022 14:52:00) deeplink to this comment

Hi Jan Karel Pieterse

I hope you can help me.

My problem is, that with one of my clients, I am trying to ad
a add-in in Excel (Office-365). But everytime i tick that box
in COM-Add-ins. The tick is gone, when i open Excel again.

Only the Excel-Add-ins is still ticked, not the COM.

I hope you can help me :)

In advance many thanks.

Joakim


Comment by: Jan Karel Pieterse (29-11-2022 15:10:00) deeplink to this comment

Hi Joakim,

Does this help: https://social.technet.microsoft.com/Forums/en-US/b2b0b9fb-d3ff-44e1-beb8-facaf9ab34e8/excel-com-addin-the-check-boxes-will-not-stay-checked


Comment by: Mohammed Al Ahmed (20-6-2023 18:33:00) deeplink to this comment

The topic is appealing to me and based on the description, I believe it would be a valuable experience to try out the tool.


Comment by: Jan Karel Pieterse (20-6-2023 19:12:00) deeplink to this comment

Hi Mohammed,

Which tool is that?


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].