Ribbon refreshes entirely when InvalideControl used (Excel)

[Burser]

Good afternoon everyone,

If you can answer this question I promise to answer threads on this forum till I've solved ten peoples problems. Even if it takes me a decade.

So - I have created a CustomUI.xml file which is working just great (I've stuck it at the end of the post, incase you want to check). The callbacks are working - I've got my reference to the ribbon UI...

Public Property Get RibbonReference() As IRibbonUI
    Dim lRibbonPointer As Long
    Dim oRibbon As Object
    
    If m_RibbonReference Is Nothing Then
        ' We've had a bit of a fall-over and need to pick ourselves up again
        ' Luckily Microsoft make this real easy... wait... that other thing
        ' Astonishingly difficult
        
        lRibbonPointer = Sheet_Config.Cells(1, 1).value
        If lRibbonPointer = 0 Then
            ' Sadly, we never got a reference to the ribbon
            Set RibbonReference = Nothing
            Exit Property
        End If
        
        Call CopyMemory(oRibbon, lRibbonPointer, 4)  ' Hopefully this memory location holds the pointer to the ribbon object
        Set m_RibbonReference = oRibbon
    End If
    
    Set RibbonReference = m_RibbonReference
End Property
Private Property Set RibbonReference(value As IRibbonUI)
    Dim lRibbonPointer As Long
    
    Set m_RibbonReference = value
    lRibbonPointer = ObjPtr(m_RibbonReference)
    
    ' Stick a pointer to the ribbon in memory
    Sheet_Config.Cells(1, 1).value = lRibbonPointer
    ThisWorkbook.Saved = True
End Property

Public Sub RibbonX_OnLoad(ByVal ribbonUI As Office.IRibbonUI)
    Set RibbonReference = ribbonUI
End Sub

... which works like a charm (don't forget to include the CopyFromMemory declaration if you're going to use that - that's at the end too).

My problem comes when I try to invalide my ribbon to update the ONE control on there that has dynamic text. The event model of my business layer calls:

Private Sub m_dbo_OracleStateChange(State As EN_DATABASE_STATE)

    ' This reloads the entire ribbon 
    ' and sets the active tab back to the Home tab
    ' I DON'T KNOW WHY!
    Call RibbonReference.InvalidateControl("bLogInOut")
End Sub

and then it all goes wrong. The user interface refeshes, sets the active tab back to Home - you can actually see my tab vanish and reappear as it is rebuilt.

Does anyone know how to make the user interface refresh and stay in the same state it was before? I just want the ONE CONTROL to change - not the entire flipping thing to reload, and reset whichever tab my user was on at the time. How's THAT useful?

I would VERY much appreciate some help - I'm close to insanity. I don't mind a flicker as it changes, I just need it to not disrupt my users workflow.

Additional code follows:

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                    (destination As Any, source As Any, ByVal length As Long)

<customUI xmlns="[URL unfurl="true"]http://schemas.microsoft.com/office/2006/01/customui"[/URL] onLoad="ThisWorkbook.RibbonX_OnLoad">
  <ribbon> 
    <tabs> 
      <tab id="tabDrilldown" label="Drilldown" visible="true" keytip="B"> 
        <group id="gExtracts" label="Data Extracts"> 
		<button id="bGetJournals" imageMso="TableInsert" size="large" label="MSA Transaction Extract (992)" onAction="ThisWorkbook.RibbonXPress" tag="TransactionExtract" keytip="C" />
		<splitButton id="sbTBMenu" size="large">
			<button id="bGetFullTB" imageMso="TableStyleTotalsRow" label="Get Most Recent TB (810)" onAction="ThisWorkbook.RibbonXPress" tag="CurrentTB" keytip="T" />
			<menu id="mTBMenu" itemSize="normal">
				<button id="bGetYtdTB" label="Get Trended TB - YTD" onAction="ThisWorkbook.RibbonXPress" tag="TrendedTBYTD" keytip="Y" />
				<button id="bGet13MB" label="Get Trended TB - 13M" onAction="ThisWorkbook.RibbonXPress" tag="TrendedTB13M" keytip="M" />
				<button id="bAdvancedTB" label="Open Advanced Form" onAction="ThisWorkbook.RibbonXPress" tag="AdvancedTBForm" keytip="A" />
			</menu>
		</splitButton>
		<button id="bBuildTree" imageMso="MacroShowAllActions" size="large" label="Mapping Trees" onAction="ThisWorkbook.RibbonXPress" tag="BuildTree" keytip="R" />
        </group>
	<group id="gJournals" label="Journal Control">
		<button id="bRaiseJournal" size="large" imageMso="CreateReport" label="Open Journal System" onAction="ThisWorkbook.RibbonXPress" tag="OpenJournalSheet" keytip="J" />
		<button id="bTransferJournal" size="large" imageMso="ImportExcel" label="Load Static Journal" onAction="ThisWorkbook.RibbonXPress" tag="LoadStaticJournal" keytip="S" />
		<button id="bCreateStaticJournal" size="large" imageMso="ExportExcel" label="Create Static Journal Sheet" onAction="ThisWorkbook.RibbonXPress" tag="CreateStaticJournal" keytip="E" />
	</group>
	<group id="gDDUsers" label="Drilldown Control">
		<button id="bCreateDataReport" imageMso="FunctionsDateTimeInsertGallery" size="large" label="Data Status Report" onAction="ThisWorkbook.RibbonXPress" tag="DataLoadReport" keytip="D" />
		<button id="bLogInOut" size="large" imageMso="DatabasePermissionsMenu" onAction="ThisWorkbook.RibbonXPress" tag="DrilldownLoginLogout" getLabel="ThisWorkbook.RibbonXGetLabel" keytip="O" />
		<button id="bChangePassword" size="large" imageMso="SetDatabasePassword" label="Change Password" onAction="ThisWorkbook.RibbonXPress" tag="ChangeDrilldownPassword" keytip="P" />
		<button id="bClearCache" size="large" imageMso="InkDeleteAllInk" label="Clear Cache" onAction="ThisWorkbook.RibbonXPress" tag="ClearCacheAll" keytip="H" />
		<button id="bCloseAddin" size="large" imageMso="PrintPreviewClose" label="Close Drilldown" onAction="ThisWorkbook.RibbonXPress" tag="CloseAddin" keytip="X" />
	</group>
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

 

[SkipVought]

hi,

I have not read your entire post, but I ask, do you not call a procedure from your button click? And if so, why access the procedure via the ribbon, when you can call it directly?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Burser]

SkipVought - TekTips legend

No one ever does read the post when there's acres of code around. I'm a sucker for posting way too much.

Any hue, I don't understand the question. The procedures there all action the controls on the ribbon. The on-action callbacks are all managed through a select case satement, and the only other procedure call there is from

My issue is that when I invalidate a control to cause it to refresh the dynamic ribbon content - the whole danged ribbon refreshes and I get punted back to the Home tab. Sadly I don't have any way of recording the screen here, so I can't demonstrate.

 

[SkipVought]

Put a break in your procedure and step thru to observe what exactly code is being executed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Burser]

Step up your game, Mr Vaught.

The code appears to be perfect^TM. All my events are firing in order, when they are supposed to. The problem seems to occur in the line:

Call RibbonReference.InvalidateControl("bLogInOut")

As far as I can make out, this is the correct usage of the command. bLogInOut is the identifier for my ribbon control - the theory is that this command will invalidate only that control and cause a re-paint to occur. This repaint will force the ribbon to re-request data from it's callbacks and update the dynamic content.

What it actually does is remove the entire custom tab from the ribbon and rebuild it. Then dump it back. This whole process occurs during the execution of that line - nothing else is involved. I added the rest of the code to the question to avoid any queries around my initialization and setup of the ribbon.

With the new content, mind - so at least that works as advertised.

 

[SkipVought]

Mr Vaught is nowhere to be found. Never heard of him. Perhaps you can find him.

My games is primarily where it interests me and where I have bandwidth, neither of which are currently true.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Burser]

I've got to say - I'm not sure there is a solution. I think it's just a fundamental part of the Office 2007 Ribbon.

The weird thing is, I can't find any other references to this behavior.