Debugging RibbonX Invalidate/InvalidateControl failures

I ran into this the other day when I was working on a file and it drove me nuts.

Background:

I always use a custom property to contain my RibbonUI object. It's set up like this:

The following code goes in the ThisWorkbook class module:

Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
'Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
'Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property

And then the onLoad statement, placed in a standard module, looks like this:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub

At that point I can invalidate the entire ribbon by using:

ThisWorkbook.ribbonUI.Invalidate

Or just a single control:

ThisWorkbook.ribbonUI.InvalidateControl "ControlName"

The Issue:

This particular problem started for me when my InvalidateControl methods weren't working. This was irritating, but I've debugged a lot of RibbonX before, so I thought it may be the control name. After checking the XML I was pretty sure it wasn't, but just for grins I switched to try to Invalidate the entire ribbon. That didn't work either…

Next up on the debugging mission was to step through the code, at which point I discovered that I had no reference to the RibbonUI object. As you've no doubt figured out if you worked with the ribbon, you can't invalidate your controls without a RibbonUI object to work with. So somewhere I thought I must be triggering an error which caused the RibbonUI to lose scope. Despite a ton of searching through the VBA though, I couldn't find anything that would trigger this kind of error.

Given that my code all looked good, I added a single line into my OnLoad statement so that it read as follows:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
Debug.Print "Fired!"
ThisWorkbook.ribbonUI = ribbon
End Sub

I then opened the file again, and found no message in the immediate window. Hmmm… The callback was obviously never called at all. This is a little strange, as this callback should be triggered as soon as the file was opened. So then I checked the remaining things that could cause these kind of issues:

• I checked the Trust Center to ensure that:
  • macros were set to "Disable With Notification"
  • The folder where the file was stored (on my network) was a Trusted Location (and that subfolders were also trusted)
• I checked that the onLoad callback matched that generated by the customUI Editor
• I check that UI errors would be displayed (just in case my XML wasn't valid). You can find this under OfficeàExcel OptionsàAdvanced near the bottom of the list:

  

None of these earned me any joy though. Things still didn't work. I then emailed the files to a friend and guess what… they worked!

With the problem isolated to my machine, I then disabled all my other add-ins and opened Excel fresh. Lo and behold, it worked!

The Fix:

When we wrote the RibbonX book, Robert and I standardized on naming the onLoad callback rxIRibbonUI_OnLoad. I'm starting to rethink that advice a bit…

In my case, I had two add-ins with this same onLoad name, one of which had the onLoad callback declared as a Public routine, rather than private. This caused a conflict when I wrote a new add-in and used the exact same procedure/callback name. Just to make this clear:

Add-in #1 used:    Public Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Add-in #2 used:    Private Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Even though Add-in #2's onLoad was correctly written, at load time it did not run. I didn't actually test this to be totally sure, but I'm 99.9% confident that Add-in #1's onLoad DID fire instead.

So some suggested rules for you if you're building an add-in:

• Code to avoid conflicts. Even if you are only writing one add-in, you never know if you'll end up installing one from somewhere else. If both you and the other author followed our naming convention, and the other author screwed up their callback by declaring it as Public, YOUR add-in will not run correctly. (Nice, eh?)
• Declare (ALL) your RibbonX callbacks as Private. This will avoid pushing this issue on someone else. By default, the customUI editor authors its callbacks as Sub blahblah(). Omitting the Private keyword by default leaves it as Public.
• Forget about Option Private Module. If you think you can use Public callbacks and just declare the module private with "Option Private Module", see rule #2. (Trust me. That one I did test, and is what led to this whole mess.)
• Add a project specific name to the end of the callback. E.g. "rxIRibbonUI_onLoad_FWBudgetTools". While I still believe in standards, you need to tag it with differentiating text to avoid conflicts with other add-ins. Oh sure, it may look long and unwieldy but let's face it -- you're only going to write this routine once per project and forget all about it after that!