Hi all
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.
I created a example workbook based on Rory’s idea on the following page :
loss of state of the global IRibbonUI Ribbon object
http://www.rondebruin.nl/ribbonstate.htm
If you have suggestion or comments let me know so I can add it to the page
Ron de Bruin
http://www.rondebruin.nl/tips.htm
Saw that post a while ago, it’s a great idea, and I’ve already implemented it in a project of mine.
Great tip!
Exactly what I’ve been looking for. Implementing right away :-)
Thanks for sharing this, Ron!
And if you’re reading this, Rory, that was a remarkable idea. Given a hundred years to ponder the situation, that approach still never would have occurred to me.
Hi, Ron
May I know where could I get the value of the pointer (518705820 in this case)? Thanks in advance.
illnino, can you be more clear
Ron,
You posted an example here . I found you use Cell “A30? to store the pointer to IRibbonUI. It reads as 520409756.
Where did you get this number? I tried to fill in other numbers. Excel crashed.
When you open the workbook it will load this callback (see the RibbonX)
RibbonOnLoad
In this callback we use this line to store the value in A30
Sheet1.Range(“A30?).Value = ObjPtr(ribbon)
Each time you open the workbook it will store the value in the cell(not the same each time)
I wish I’d known about this trick a long time ago. Something else that was interesting in the Mr. Excel post by Rory was the line “guiRibbon.Invalidate ‘Control (“tabCustom”) InvalidateControl does not work reliably”. Good to know.
Hi Ron
I hope you would be able to advice
when using excel on the first instance the dialog box for the format cells takes about 2minutes and 8 seconds to pop -up
Once the dialog box is activated closing and reopening the format cells dialog box is not a problem even if it is in the same workbook
Hi Ron,
I’m working on Office 2007 + Windows 7. The ‘CopyMemory’ when it is called, crashes my Excel. The sample file you’ve posted in your site is working fine though. I can’t really make it out. Oh by the way, I am saving the return value of ObjPtr(ribbon) to the workbook.customdocumentproperties (instead of a cell), but that shouldn’t be a problem right? (or should it!?)