How to preserve or regain the Id of my custom ribbon UI?

[WernerGg]

I have a little Excel 2007 application with a standard ribbon UI. See the CustomUI.xml and the VBA-code below.

I have uploaded an Excel 2007 TestRibbonUI.xlsm to box.net from where you can get it with the link http://www.box.net/shared/8uznug7s3r

My new tab with name "My Tab" and id="tabCustom" has a group "grpToggle" with two buttons "btn1" and "btn2". Their enabled-states shall be controlled at runtime. Pressing any of them calles "DoButton" which just toggles both states.

The problem is that the UI is initialized only once during load. To change enabled-state or visibility, controls must be forced to reinitialize. For that we have to store the UIs Id during load by means of the o n L o a d -callback (sorry, the vBulletin-software replaces onL... with asterix. God knows why. Is this a dirty word?)

But this Id can only be stored in a static variable (Private guiRibbon As IRibbonUI), which gets lost after errors or during reset from the VBA-IDE.

The third button "Force Error" in our group just produces a zero division. After that the ribbon UI does no longer work and the workbook must be closed and reopen by the user.

Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?

In the VBA help we can find an example which uses a method "guiRibbon.Refresh". I think this is exactly what we would need instead of the current ribbon UI design which is more than weak in that point. Unfortunatly that Refresh is not implemented.

Sorry I am not able to enter the xml-code and not the VBA code within code-tags. The vBulletin software always corrupts the things. Stupid!

 

[RoryA]

You're welcome!

 

[hatman]

Just exactly, perfectly what I was looking for.

Thanx Rory!

 

[sebastienm]

Hi Rory,

First, thank you! I've been looking for this code for quite a while, probably like most users.
Now, is there a chance that the pointer changes over time without the change being captured?
(I remember pointers held for too long being an issue with many apis in the past)

I was thinking about storing the value in Excel's Hidden Name Space:
<tt>

''' Write the pointer 
Application.ExecuteExcel4Macro   _
    "SET.NAME(""RibbonXPointer"",lngRibPtr)"
  
''' Read the pointer
</tt><tt>lngRibPtr=</tt><tt>Application.ExecuteExcel4Macro("</tt><tt>RibbonXPointer</tt><tt>")</tt>

<tt>
The advantages would be:
- the value of the pointer is centralized and shared across all books, and during the entire Excel session, even when all books are closed but excel is kept open.
- when a book opens or when it rebuilds the ribbon AND when it has ribbon code, the pointer value can be updated for all books to use (?less chance of a bad pointer?)

Thanks,
Sebastien
</tt>

 

[hatman]

Hi Rory,

<TT>
The advantages would be:
- the value of the pointer is centralized and shared across all books, and during the entire Excel session, even when all books are closed but excel is kept open.
- when a book opens or when it rebuilds the ribbon AND when it has ribbon code, the pointer value can be updated for all books to use (?less chance of a bad pointer?)</TT>

Why do othe rworkbooks need access to the pointer? A ribbon object is typically created by the xml portion of a single workbook. Multiple workbooks may share the same tab, but each workbook essentially creates and manages it's own ribbon object within that tab. Withini that context, the workbook that "owns" the ribbon object may use the application object to monitor all open workbooks, respond to actions/events in those other workbooks, and thereby make runtime changes to the ribbon object. But it is my belief that trying to build code in multiple workbooks that each individually excercise control over the same ribbon object would be difficult to manage and probably cause conflicts that would be difficult to manage, especially in light of the fact that programatically, the configuration of the ribbon is fundamentally invisible to the VBA component of a workbook.

While the pointer value can be stored anywhere it will remain static, in my opinion, the use of an XL4 macro (included for backwards compatibility, though occasionally leveraged to accomplish otherwise deprecated functionality) is probably overkill. Visiblility to all workbooks probably won;t hurt, but is it really necessary? I have used Rory's code to place the pointer value in a workbook named range within the workbook where the code to create and manipulate the ribbon object is housed... and it works flawlessly. If the users are allowed to access to the workbook named ranges, the I would suggest making it invisible (ther esia a switch for that) but other than that, I wouldn;t recommend making it any more global than that.

 

[sebastienm]

>>>> the pointer value [...] it will remain static.
Ha, ok, i wasn't sure of that.

>>>each workbook essentially creates and manages it's own ribbon object.
I wasn't aware of that either. So am i understanding correctly: each book receives a different RibbonX object ie pointer?


Now, the rest of the post was mostly assuming that it wouldn't be static and that the pointer was the same for all books, in which case getting the latest pointer value more frequently and in a centralized way would have been valuable; at least in my case and my users'. Anyway.

>>> Application.XL4Macro [...] overkill
I don;t think so personally.


Thank you,

Sebastien

 

[WernerGg]

I described the solution in my post #9. It stores the object reference in a defined name and I think this is allright and causes no problems whatsoever.

In my productive version the hole thing is in a module of an Excel-Addin (gizDCOExcel.xlam) and I use a name "gizDCoExcel_IRibbonUI_Ptr" for storage of the pointer. This name is created on the fly if not yet defined.

The code reads:

' -------------- Savely store and retrieve object reference. Used for IRibbonUI
Private Const C_OBJ_STORAGENAME As String = "gizDCoExcel_IRibbonUI_Ptr"
Private Declare Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
 
Public Function StoreObjRef(obj As Object) As Boolean
' Serialize and savely store an object reference
    StoreObjRef = False
 
    ' Serialize
    Dim objName As Name, longObj As Long
    longObj = ObjPtr(obj)
 
    ' Store into a defined name
    Set objName = CheckForName(C_OBJ_STORAGENAME)
    objName.value = longObj   ' Value is "=4711"
    'Debug.Print "Save storage """; C_OBJ_STORAGENAME; """ stored the object reference"; longObj
 
    ' Return
    StoreObjRef = True
    Set objName = Nothing
End Function
Public Function RetrieveObjRef() As Object
' Retrieve from save storage, deserialize and return the object reference
' stored with StoreObjRef
    Set RetrieveObjRef = Nothing
 
    ' Retrieve from a defined name
    Dim objName As Name, longObj As Long
    Set objName = CheckForName(C_OBJ_STORAGENAME)
    longObj = Mid(objName.value, 2)
    'Debug.Print "Object reference"; longObj; "retrieved from save storage """; C_OBJ_STORAGENAME; """"
 
    ' Deserialize
    Dim obj As Object
    CopyMemory obj, longObj, 4
 
    ' Return
    Set RetrieveObjRef = obj
    Set obj = Nothing
    Set objName = Nothing
End Function
Private Function CheckForName(aName As String) As Object
' Check if a defined name exists in this Workbook. If not create it with value 0.
' Return the Name-object
    Dim objName As Name
    With ThisWorkbook
        On Error Resume Next
        Set objName = .names(aName)
        On Error GoTo 0
        If objName Is Nothing Then
            Set objName = .names.Add(Name:=aName, RefersTo:=0)
        End If
    End With
    Set CheckForName = objName
    Set objName = Nothing
End Function

 

[pobel]

In order to use this function across the entire Office Suite, It is possible to save the pointer to the Ribbon in the window Property List. CPearson used this to create Truly Global Variables. These are also maintained across VBA code breaks.

Full description and sample code: http://code.ecomerc.com/Articles/VBA_Ribbon/

Just noticing the problems regarding different Ribbons, it should be possible to link the active ribbon pointer to the active window of excel (since I recall that each workbook windows is an actual windows window. Thus maintaining the specific pointer for the specific ribbon. The code should then be adjusted in the GetDefaultHandle() but I have not tested this.

Peter

 

[RobExcel]

Rory, I've just found your post, a year later. Brilliant! Thank you!
Rob

 

[hatman]

This worked well when I first rolled it out in my application. Lately, it's been mis-behaving, and I can't duplicate the problem to track why. Pertinent code:

Sub Callback_LabStatusRibbon_******(ribbon As IRibbonUI)
    Dim lngRibPtr As Long
    SetActiveWindow Application.hwnd
 
    Application.SendKeys "%HAM{RETURN}"
    Set grxLabStatusRibbon = ribbon
 
    lngRibPtr = ObjPtr(ribbon)
 
    If lngRibPtr = 0 Then
 
        MsgBox "Unrecoverable error encountered.  Please close and re-open this file.  If this error persists, contact Paul Sasur at 860-654-2506.", vbCritical, "Error in LabStatusRibbon_******"
 
    End If
 
    ThisWorkbook.Names("Ribbon_UI_Pointer").Value = "=" & lngRibPtr
End Sub

Function Get_Ribbon(lngRibPtr As Long) As Object
   Dim objRibbon As Object
   CopyMemory objRibbon, lngRibPtr, 4
   Set Get_Ribbon = objRibbon
   Set objRibbon = Nothing
End Function

Sub inval_DD_1()
    If grxLabStatusRibbon Is Nothing And Evaluate(ThisWorkbook.Names("Ribbon_UI_Pointer").Value) <> 0 Then
 
        Set grxLabStatusRibbon = Get_Ribbon(Evaluate(ThisWorkbook.Names("Ribbon_UI_Pointer").Value))
    End If
 
    grxLabStatusRibbon.InvalidateControl "Location_Selection"
End Sub

For some strange reason lately, I have been getting complaints from users that they encounter a debug error on the invalidate control command. In the occassional instances where I have been nearby when it has happenned, I have observed that grxLabStatusRibbon is NOTHING and ThisWorkbook.Names("Ribbon_UI_Pointer").Value = "=0". This makes no sense to me as teh ONLY place in teh project where I assign a value to the name is in the ****** routine... but the error message does not get raised. I am currently working through my project to all of teh places where I invalidate the ribbon, and I am handling the error with more grace... but that doesn't fix the problem. Anyone have any thoughts?

 

[RoryA]

My guess would be that your o n L o a d callback is not being called in the first place. Do you use the same callback name for all your applications?