Avoid losing the state of the global IRibbonUI ribbon object

In today’s post, Excel MVP Ron de Bruin provides a solution for a common problem that occurs when you use RibbonX and VBA to customize the ribbon. You can find more tips and links to Excel add-ins at Ron’s website: http://www.rondebruin.nl/.

See more light bulb photos at Office.com/imagesThere is a common problem when you are working with the ribbon by using RibbonX and VBA. The ribbon object (which is initialized when Excel loads your file) can lose its reference to the ribbon, which means your code can no longer tell Excel to update your ribbon customizations.
There is simply no easy, built-in way to recover the handle to the ribbon when there are problems in or with your code. The only way to fix it is to close and reopen your workbook, not a very user-friendly way.

But MVP Rory Archibald came up with a great idea in a post in the MrExcel forum. The example in the workbook that you can download on this page (see below) is based on Rory’s idea and works in Excel 2007 32-bit and Excel 2010 32-bit and 64-bit.

Store the pointer to the IRibbonUI in a cell (or Name or wherever) and then use CopyMemory to get it back when the state is lost.

You can use this example workbook to test this method to recover the ribbon handle if it is lost.

Download: Loss of Ribbon state.zip.  

Note: I decided not to add all the workbook code and RibbonX to this page, because when you see the code working in the workbook, it is much clearer. If this is all new to you, I suggest that you click the link below to start with my ribbon page.

For more information about how to customize the ribbon, see Change the Ribbon in Excel 2007 or Excel 2010.

–Ron de Bruin