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 and higher (32 and 64 bit) and also in Mac Excel. 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. MVP Jan Karel Pieterse sent me the API that is working on the Mac so the download on this page is working in Excel for Windows and in Excel for the Mac.

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

Download Example file : Loss of Ribbon state.zip