Search code examples
xmlexcelinvalidationribbonxvba

Excel 2013 Invalidate Ribbon control not working

I'm trying invalidate a dropdown on my Custom UI Ribbon I created in Excel 2013. I basically want to be able to force the drop down to reload and trigger its load event. I have added the following "onLoad" to my XML code header:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="MyAddInInitialize">

I have added the following VBA to my workbook:

Dim MyRibbon As IRibbonUI

Sub MyAddInInitialize(Ribbon As IRibbonUI)
    Set MyRibbon = Ribbon
End Sub

Sub myFunction()
    'Invalidates the caches of all of this add-in’s controls
    MyRibbon.InvalidateControl ()
End Sub

I received this info from https://msdn.microsoft.com/en-us/library/microsoft.office.core.iribbonui.invalidate.aspx

But I can't quite figure out how to make it work. I keep getting a "Compile error Expected:=" error wen running the Sub "myFunction". I have tried putting the dropdown ID inside the () in quotes:

MyRibbon.InvalidateControl ("CIB_Dropdown")

But, it still doesn't work and gives me the error "Object Variable or with block not set". I have tried so many things and am at a loss here. What am I missing here?

Solution

  • Answer found at this link worked like a charm!!!!

    https://www.office-forums.com/threads/resetting-dropdown-list-in-ribbon.2169931/