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

All comments about this page:


Comment by: Patrick Matthews (17-8-2016 19:48:59) deeplink to this comment

Blocking all downloaded files by default? That seems rather heavyhanded...


Comment by: Jan Karel Pieterse (18-8-2016 06:20:49) deeplink to this comment

Hi Patrick,

I'm not sure yet whether it is all files, or just add-ins. I have mixed results when testing.


Comment by: Gunthor (19-10-2016 10:43:36) deeplink to this comment

T H A N K Y O U
I've been looking for this answer for 2 months...
M A N Y T H A N K S :-)


Comment by: Ralph (7-2-2017 16:00:17) deeplink to this comment

THANK YOU!!! I've been searching for this solution for a long, long-time!


Comment by: Wei (17-2-2017 07:37:12) deeplink to this comment

I tried, but still failed, I also tried to update the regedit, move the dll file to a safe location. Almost feel crazy.......


Comment by: Jan Karel Pieterse (20-2-2017 09:57:33) deeplink to this comment

Hi Wei,

The article is about Excel-based add-ins (xla and xlam files), not about dll files.


Comment by: king_faisal (1-10-2017 01:03:12) deeplink to this comment

We are using Dose for Excel Add-In that provides more than +100 functions and features specialized in Excel editing and data manipulating, please consider this add-in in your post since it is so useful for Excel end-users.

https://www.zbrainsoft.com/Features.html


Comment by: James Witherspoon (26-12-2017 19:57:51) deeplink to this comment

Excel 2003 no longer can load Name Manager. When trying to install the addin I get this error from Microsoft Visual Basic: "Errors occurred during load". Clicking the "help" button says to check the log file but I have no idea where that file is located. I unblocked the Name Manger.xla file, as instructed, but that did not help.


Comment by: Jan Karel Pieterse (27-12-2017 11:24:58) deeplink to this comment

Hi James,

What happens if you launch Excel first (from its shortcut)?


Comment by: Doug Hesketh (8-2-2018 09:45:56) deeplink to this comment

This was exactly the cause of my issue, a simple resolution and my frustration has disappeared. Thank you.


Comment by: Phil Sousa (16-3-2018 22:12:24) deeplink to this comment

Thanks very much for this fix!!! It worked! Woohooo!


Comment by: Jan Karel Pieterse (17-3-2018 13:36:08) deeplink to this comment

Hi Phil,

You're welcome!


Comment by: Dan Kelly (2-4-2018 10:12:17) deeplink to this comment

Using Excel 2016 on windows 10.
I was trying to add the JKP Name Manager add-in. Tried unblocking on the file properties, adding the add-in location as a trusted location and still nothing.
Later, I found out that Excel decided to put the Name Manager addin in the Formulas tab as a separate group. Didn't think to look there.


Comment by: Jan Karel Pieterse (3-4-2018 09:56:56) deeplink to this comment

Hi Dan,

The NM manual mentions where to find it in the user interface, this manual is part of the download containing the add-in :-)


Comment by: Ronald LaRiviere (25-5-2018 14:58:56) deeplink to this comment

We are using Excel 2016 and I cannot find the Name Manager Utility in the application. I checked the properties as described on the website:

https://www.jkp-ads.com/Articles/excel-add-ins-fail-to-load.asp

However, there is no Security Block section. The Add-in is listed. What else can I do to have access?

Ron


Comment by: Jan Karel Pieterse (25-5-2018 15:38:51) deeplink to this comment

Hi Ronald,

If you open Excel and subsequently the VBA Editor, is Name Manager listed in the list of projects?


Comment by: Ronald LaRiviere (25-5-2018 17:12:50) deeplink to this comment

Jan,

It is listed NameManager (Name Manager 2007.xlam)

Ron


Comment by: Jan Karel Pieterse (27-5-2018 11:32:28) deeplink to this comment

Hi Ronald,

Does control+shift+n open the name manager dialog? The UI can be normally found on the formulas tab of the ribbon, far right.


Comment by: Ron LaRiviere (29-5-2018 13:52:57) deeplink to this comment

When selecting Cntrl+Shift+n, the Name Manager Utility is there. Looking at the formulas tab, the icon is not and it is not in the selection list to add.


Comment by: Jan Karel Pieterse (29-5-2018 14:12:59) deeplink to this comment

Hi Ron,

If you enable show add-in userinterface errors (Excel options) and you reboot Excel, do you get any error messages?


Comment by: Ronald LaRiviere (29-5-2018 14:55:25) deeplink to this comment

I selected the options in options and when I open Excel, I get a Custom UI Error dialog box that reads as follows:

The custom UI associated with this document has been disabled by policy.


Comment by: Jan Karel Pieterse (29-5-2018 15:19:28) deeplink to this comment

Hi Ron,

Which is tech-speak which means your IT dept has decided for you that you do not want external tools to work properly. Unfortunately there isn't anything I can do about that :-(


Comment by: PhilUK (22-6-2018 10:44:37) deeplink to this comment

Hi Jan
Like many of your followers I have used your addin since Excel 2003 and have appreciated your work for many years
I am now using Excel 2016
When I try to add a new name I get an error message headed "API Clipboard Copy", which states "Memory location could not be Unlocked. Clipboard copy aborted"
The added name is then listed but typically shows as referring to the active cell only
I have tried to edit the "refers to" but the same error message appears
Can you offer any advice?


Comment by: Ricardo Hernandez Tablas (19-7-2018 18:30:32) deeplink to this comment

Excellent article, helped me a lot with some problems I was having with add-ins.

Thanks a lot


Comment by: Aslam (18-10-2018 08:19:12) deeplink to this comment

I was also having problem with Add-Ins and search alot of articles in this regard but didn't get any solution about, but this article helped me by Right Clicking property of add-ins and Unblocked the addins. now its working FINE many many thanks to the team.


Comment by: joan jiang (2-11-2018 20:38:01) deeplink to this comment

how many ways to insert the date picker function?


Comment by: Eddy Van Wulpen (2-7-2019 19:36:00) deeplink to this comment

Great,

only when I protect the sheet it doens't work,
even if you unlock the cell

Thank for helping me out


Comment by: Jan Karel Pieterse (3-7-2019 11:05:00) deeplink to this comment

Hi Eddy,

I'm not sure what you are referring to?


Comment by: Eddy Van Wulpen (3-7-2019 14:01:00) deeplink to this comment

Hi Jan Karel,

put a date in a cell
DatePicker works
Go to Cell Properties>Properties>Unlock
Now Protect sheet>Unlocked cells only
DatePicker failed

Solution:
Protect sheet>Unlocked cells + Edit objects
DatePicker works
Disadvantage: all other objects in the sheet can be moved or changed


Comment by: Jan Karel Pieterse (3-7-2019 16:17:00) deeplink to this comment

Hi Eddy,

Hmm, This is probably implementation-specific and has nothing to do with the page (this page) you placed your comment on. Depending on how this DatePicker works, the code will need to be adjusted.


Comment by: Adam Alhayek (25-11-2019 16:33:00) deeplink to this comment

hi

when i try to add date i get msg " run-time error '13'
" type mismatch"


Comment by: Jan Karel Pieterse (25-11-2019 16:42:00) deeplink to this comment

Hi Adam,

When exactly do you get this message? Is it with any of my downloads? If so, which one?


Comment by: Mollie (29-4-2020 08:19:00) deeplink to this comment

The latest version of Excel for Office 365 doesn't even show the "security....unblock" option in the properties of the date picker. Is there a newer workaround to have the datepicker reliably show up?
Note: I couldn't unzip to the suggested xlstart folder, because my Microsoft Office isn't listed in the x86 Program folder. Any advice?


Comment by: Jan Karel Pieterse (29-4-2020 10:18:00) deeplink to this comment

Hi Mollie,

Whether or not you see the Unblock button/checkbox is not caused by Office but rather by Windows I'm guessing. You are aware that you should be accessing the properties dialog from Windows Explorer?


Comment by: Carlos Ferreira (25-5-2020 18:16:00) deeplink to this comment

I need it on VBA.


Comment by: Jan Karel Pieterse (26-5-2020 11:02:00) deeplink to this comment

Hi Carlos,

Can you please elaborate what you "need on VBA"?


Comment by: Renny (11-6-2020 16:24:00) deeplink to this comment

Hello,

I also get the error message "run-time error 13"

" type mismatch"

I run Office 16.

Appreciate any suggestions or help.

Thanks


Comment by: Jan Karel Pieterse (12-6-2020 12:04:00) deeplink to this comment

Hi Renny,

When do you get this error (opening what workbook and on which line of VBA code precisely)?


Comment by: Mike (11-8-2020 08:36:00) deeplink to this comment

Many thanks for this helpful description!


Comment by: abu (27-8-2020 22:50:00) deeplink to this comment

run time error 91 (vb) object variable not set


Comment by: Tim (24-9-2020 07:19:00) deeplink to this comment

Many thanks Jan, this is exactly what I needed. I recently switched to a new PC and was going crazy without my trusty Excel add-in full of secret tools and functions!


Comment by: suraj kumar (27-9-2020 03:48:00) deeplink to this comment

I have added addin but it failed to display at forms control. How to add it in form control even it is not in other controls list


Comment by: Jan Karel Pieterse (28-9-2020 10:19:00) deeplink to this comment

Hi Suraj,

Which control are you trying to use exactly?


Comment by: Hans (12-10-2020 15:47:00) deeplink to this comment

Is it possible to make the Date Picker show Monday first?

Many kind regards

Hans


Comment by: Jan Karel Pieterse (13-10-2020 14:10:00) deeplink to this comment

Hi Hans,

Which date picker are you referring to please?


Comment by: Yasir khan (26-10-2020 23:13:00) deeplink to this comment

hi
hats off to your great work


Comment by: Justin C (12-11-2020 16:52:00) deeplink to this comment

so im trying to install the sutosave addin for excel 2010 on windows 8. Whenever i try to unblock the .XLA file it doesnt give me the option to unblock the file in the general tab in properties menu. After browsing through the security tab i still cannot find the option to unblock the .XLA file. Any advice? thanks


Comment by: Jan Karel Pieterse (13-11-2020 13:54:00) deeplink to this comment

Hi Justin,

Perhaps it does not need unblocking. Does the add-in work? It should display the Add-ins tab of your ribbon where a button "Autosafe Settings" should appear.


Comment by: endang.supartini.66@gmail.com (1-3-2021 04:49:00) deeplink to this comment

Thank you, its working!
Ive had casses when using excel add in Stat 97. Where the file is ok, also it was active in setting. Just not wanna open. When i unblock it went open.


Comment by: Mike Cauley (24-11-2021 23:06:00) deeplink to this comment

Hi Sam, I am running on a MAC with the full Excel version 2019 (not 365) and cannot get your app to work, seems to have installed but nothing after that.

Mike Cauley
mike.cauley@atlanticbb.net


Comment by: Jan Karel Pieterse (25-11-2021 14:34:00) deeplink to this comment

Hi Mike,

Which add-in (or Excel file) are you referring to please?


Comment by: Mike Cauley (25-11-2021 16:01:00) deeplink to this comment

I am referring to the Date Picker App, a thought that I had is the macros being disabled, I have googled how to remove app as well and start over, but those instructions don't apply to this version of Excel.

Again, MAC with the full Excel version 2019 (not 365) Version 16.55, all updates are installed.

Thanks so much,

Mike Cauley
mike.cauley@atlanticbb.net


Comment by: Jan Karel Pieterse (25-11-2021 16:10:00) deeplink to this comment

Hi Mike,

I do not offer a date picker app. I refer to one, but the app itself is not mine!


Comment by: Paulo Lamy Roque (31-1-2022 14:14:00) deeplink to this comment

Hello all,

Every time that I try to use this add-in appear a message saying "Microsoft Visual Basic Run-Time error'13': Type mismatch"

Already try to remove the add-in but I couldn´t, try to install back again the the problem continues


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

Hi Paulo,

Which add-in is giving you that error please?


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


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