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!

 

[andypugh]

Are you willing to share this?

It's nothing very clever or complicated.

When the ribbon handle is saved

HiddenSheet.range("A1") = ObjPtr(Ribbon)
HiddenSheet.range("A2") = ObjPtr(Application)

Then my clsNonVolRibbon Invalidate method is:

if myRibbon is Nothing then
    if HiddenSheet.Range("A1") <> "" and HiddenSheet.Range("A2")==ObjPtr(Application) then
       ' Code to recover ribbon from pointer
    Else
       MsgBox "Can't recover the Ribbon"
       Exit Sub
    End If
End If
myRibbon.Invalidate

 

[crewaustin66]

Rory,

Thank you for posting this solution. I haven't quite gotten it to work and I need some assistance.

1. Where do I put the code that gets the pointer to the RibbonUI?
2. In your example, you store a guiRibbon object based on the incoming ribbon object. I didn't see it used. Why is it there?

' This is done once during load of UI. I.e. during workbook open.
    Set guiRibbon = ribbon

Thank you,
Crew

OK, you got me thinking! It occurred to me that if you got a pointer to the IRibbonUI and stored it in a cell (or Name or wherever) then you could use CopyMemory to get it back again. Seems to work in a quick test:

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

Public Sub ribbon L o a ded(ribbon As IRibbonUI)
   ' Store pointer to IRibbonUI
   Dim lngRibPtr As Long
' Store the custom ribbon UI Id in a static variable.
' This is done once during load of UI. I.e. during workbook open.
    Set guiRibbon = ribbon
    lngRibPtr = ObjPtr(ribbon)
    ' Write pointer to worksheet for safe keeping
    Tabelle2.Range("A1").Value = lngRibPtr
End Sub
Function GetRibbon(lngRibPtr as Long) As Object
   Dim objRibbon As Object
   CopyMemory objRibbon, lngRibPtr, 4
   Set GetRibbon = objRibbon
   ' clean up invalid object
   CopyMemory objRibbon, 0&, 4
   Set objRibbon = Nothing
End Function

 

[hatman]

This thread assumes you aready have code in place that uses the Ribbon object. The only method available for this object is .Invalidate, which basically causes the ribbon to re-load. The ribbon (or a single ribbon control) is typically reloaded/invalidated when something has changed that would drive a different return value to one or more of the GetXXXX callbacks of one of the properties of the Ribbon or one of it's children, for instance, depending upon the object selected by teh user, a particular bitton may be enabled or disabled. here is an example]

 

[crewaustin66]

Thanks for all of the help. I was finally able to get my menu working as desired. The only problem that I saw is tat ****** only runs one time when the application starts. The menu works on that workbook. However, it does not work on the creation of subsequent workbooks. Therefore, to persist the ribbon pointer for use by all workbooks, I store and retrieve the ribbon pointer by using the registry.

Crew

 

[alketrazz]

This problem is solved now. Thanks to rorya!

I have uploaded TestRibbonUI_3.xslm to Box.net http://www.box.net/shared/8omsyxrkmj. Sheet IRibbonUI explains the problem and the solution.

[/CODE]

Hi

This won't work on 64 bit system. I searched the error on "Compile error: The code in this project must be updated for use on 64-bit systems" error message when you edit a VBA macro in the 64-bit version of an Office 2010 program

And tried to change the declare bit as follows

#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As LongPtr)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
#End If

That got passed that error but then I get a Type Mismatch error when reopening the workbook.

the line longObj = ObjPtr(obj) is highlighted in the following code.

Public Function StoreObjRef(obj As Object) As Boolean
' Serialize and savely store an object reference


    StoreObjRef = False
    
    ' Serialize
    Dim longObj As Long
    longObj = ObjPtr(obj)
    
    ' Store into a defined name
    If IsNumeric(Range(C_OBJ_STORAGENAME)) Then
        Range(C_OBJ_STORAGENAME) = longObj
        Debug.Print "Save storage """; C_OBJ_STORAGENAME; """ stored the object reference"; longObj
    End If
    
    ' Return
    StoreObjRef = True
End Function

How do I get this to work in 64 bit excel?
Thanks

 

[ktrasler]

Hi

I've sorted it

I had to change the declaration of the longObj

Dim longObj As Long

to

Dim longObj As LongPtr

Also I'd put the Ptr in the declare statement which wasn't needed. It should have been this...

#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
#End If

Thanks

 

[chazz]

What a bunch of unholy freaking hogwash.

They "improved" Excel by doing away with simple, programmable toolbars that even a hack like me could work?

 

[MrBillBenson]

When I tried to implement this in a class module, I found that the same kinds of thing that would cause my ribbon to lose state - were causing my class module to lose state, and therefore any methods I kept within the class - like retrival of the memory pointer and reading from or assigning that to a defined name or spreadsheet range was becoming fruitless. Also, I had migrated the .Invalidate call to a method within the class as well, therefore I have to ask - in what way is this "nonVolatile" ?


I have gone back to retrieving the ribbon out of the defined name as part of a procedure in a standard module.

If you could send me a full example of your class and some workbook code that implements it, I would be very agreeable to re-thinking my hasty retreat, because it seems like you ought to be adding robustness your way, not taking a step forward and a step back, and I don't want to draw an invalid conclusion!

Thank you.
m r b i l l b e n s o n a t g m a i l . c o m

 

[Jibse]

Thanks RoryA !

I store the pointer into my code

Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents("Welcome").CodeModule
SheetCodeModule.ReplaceLine 60, "'" & lngRibPtr

And, to get it :

Set SheetCodeModule = ThisWorkbook.VBProject.VBComponents("Welcome").CodeModule
GetRibbon CLng(Mid(SheetCodeModule.Lines(60, 1), 2))

 

[Jibse]

Not working if the VBA project is protected.
But I store the pointer in ThisWorkbook.Worksheets(1).Range("A1"), ThisWorkbook is my Addin. I don't know where is this Worksheets(1), I can't see it, but it works. Is it a good solution ? Thanks.