Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

losing the state of the global IRibbonUI ribbon object

There is a common problem when you are working with the Ribbon with RibbonX and VBA. The ribbon object (which is initialized when Excel loads your file) can loose 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 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 is based on Rory's idea and works in Excel 2007 32 bit and Excel 2010-2016 32 and 64 bit. Idea: Store the pointer to the IRibbonUI in a cell (or Name or wherever) 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 : Check out also the pages on the site of MVP Tushar Mehta about this common problem

Save a global variable in an Excel workbook

Protect a global variable in another VB project