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:
You’ll get a security prompt if you work with standard Excel files with .xlsx or .xlsm extensions.
If you use 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
After installation, this is the most efficient method to fix the disappearing add-in ribbon. 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.
After that,
- 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. Then, close the Trust Center Window.
- Then, close and restart Microsoft Excel.
Tip: You find the default AddIns folder under the following path:
C:\Users\<your_username>\AppData\Roaming\Microsoft\AddIns
This folder is not trusted by default, so you must 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
We’ll get an error message by clicking on the newly created ribbon item (to start the “test_only” macro.
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.