A recent Microsoft Office security update (for July 2016) has caused add-in Ribbon Menu Bars to disappear when Excel is opened. What a frustrating issue! This article explains the possible solutions to fix it.
Good to know when you download the add-in from the internet, it will be blocked by default.
Let us see the difference between add-in files and regular Excel files:
If you are working with regular Excel files with .xlsx or .xlsm extensions, you’ll get a security prompt.
If you are using add-in files (.xla or .xlam files), you’ll be flying blind, because you’ll never be faced with a security prompt. So we will not have any idea why the add-in Ribbon disappeared.
Are you using an Excel add-in that keeps disappearing? Before we dive in, learn more about the disappearing ribbon effect, and check out our video:
Solution 1: Move Add-in files to a Trusted Location
This is the most efficient method to fix the disappearing add-in ribbon after installation. We recommend moving your downloaded add-in folder to the trusted locations list.
Open the Trusted Locations menu and use the following path: File > Options > Trust Center > Trust Center Settings > Trusted Locations.
- Click the “Add new location” button.
- Click the “Browse” button.
- Jump to the folder that contains the add-in. Click OK.
- Click the “Subfolders of this location are also trusted” checkbox if you are using subfolders.
- Press the OK button twice. Close the Trust Center Window.
- Close and restart Microsoft Excel.
Tip: You find the default AddIns folder under the following path:
This folder is not trusted by default so you have to add it to the Trusted Locations list.
Unblock the Add-in file
If the Trusted location method does not help, we try to figure something out.
To Unblock the File, please follow the steps below:
- Right-click the add-in file (.xla or .xlam) and select Properties.
- Check the Security section of the General tab. Check the Unblock box.
- Finally, click the OK button.
Just close Excel and re-open it, the add-in and the custom ribbons will appear.
Use proper syntax in CustomUI.xml
Here is a sample customUI.xml content:
<?xml version="1.0" encoding="utf-8"?> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab id="TestTab" label="Test tab" > <group id="TestGroup" label="Test group" > <button id="test" visible="true" size="large" label="Test Sub" onAction="test_only" /> </group> </tab> </tabs> </ribbon> </customUI>
For testing purposes, I’ve just created one sub in a module:
Sub test_only() MsgBox "test" End Sub
Clicking on the newly created ribbon item (to start the “test_only” macro, we’ll get an error message.
The solution is simple.
Add ‘control As IRibbonControl‘ as an argument to your subroutine. For example, if the name of your subroutine is ‘test_only ‘, then use this code:
Sub test_only(control As IRibbonControl) 'your code goes here End Sub
Office Group Policy – Custom UI Ribbon Blocking
To update the default group policy setting that is preventing the button from being added:
- Start gpedit.msc
- Navigate to User Configuration > Administrative Templates > Microsoft Office > Global Options > Customize
- Now Disable UI Extending from documents and templates
- Uncheck “Disallow in Excel”
- Close gpedit
- Apply the “gpupdate /force” command to update the change on the local machine
- Finally, launch Excel and check that the Solver add-in is displayed in the Data -> Analysis ribbon