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

WernerGg

WernerGg

New Member
Joined
Oct 19, 2010
Messages
43
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
WernerGg said:
Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?
Click to expand...

No, I'm afraid not.
 
Upvote 0
rorya said:
No, I'm afraid not.
Click to expand...

I think about serializing the "guiRibbon As IRibbonUI" and write it to some persistent storage (file, worksheet cell, workbook defined name, DLL or the like). The problem is that I can convert an Object-variable to some VBA datatype for serialization (e.g. Object -> Long) but not vice versa (e.g. Long -> Object).

In a classical programming language one would use an overlay for that. But in VBA I know no means. The solution would be a little DLL which does that in C or so. But I have no such DLL.

Maybe you remember some similar thing?
 
Upvote 0
No, I really did mean there is no way to do it, at least not that I have ever seen mentioned by any of the people, including many MVPs, who have discussed it.
 
Upvote 0
rorya said:
No, I really did mean there is no way to do it, at least not that I have ever seen mentioned by any of the people, including many MVPs, who have discussed it.
Click to expand...

Hmm, sad. I think it means that one cannot use the ribbon UI in any dynamic sense (visibility, enablement, process dependent widgets, etc). Apart from trivial problems it means that one cannot use it at all.

Do you know any statement from Microsoft? For them it would be trivial to solve that. Maybe a new method "Workbook.GetRibbonID". Is the situation better in Office 2010?
 
Upvote 0
No, the situation is the same in 2010 as far as I know. Since MS' primary focus is more on .Net than on VBA, I don't necessarily expect to see a resolution soon, but fingers crossed for Office 15...
 
Upvote 0
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:
Code: 
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
End Function

Then:
Code: 
Public Sub DoButton(ByVal control As IRibbonControl)
' The onAction callback for btn1 and btn2
   
    ' Toggle state
    Toggle12 = Not Toggle12
   
    ' Invalidate the ribbon UI so that the enabled-states get reloaded
    If Not (guiRibbon Is Nothing) Then
        ' Invalidate will force the UI to reload and thereby ask for their enabled-states
        guiRibbon.Invalidate 'Control ("tabCustom") InvalidateControl does not work reliably
    Else
      Set guiRibbon = GetRibbon(CLng(Tabelle2.Range("A1").Value))
      guiRibbon.Invalidate
        ' The static guiRibbon-variable was meanwhile lost
'        MsgBox "Due to a design flaw in the architecture of the MS ribbon UI you have to close " & _
'            "and reopen this workbook." & vbNewLine & vbNewLine & _
'            "Very sorry about that.", vbExclamation + vbOKOnly
      MsgBox "Hopefully this is sorted now?"
        ' Note: In the help we can find
        ' guiRibbon.Refresh
        ' but unfortunately this is not implemented.
        ' It is exactly what we should have instead of that brute force reload mechanism.
    End If
   
End Sub
 
Last edited:
Upvote 0
Solution
rorya said:
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:
Click to expand...

Whow! That's exactly what we need. I will build that into my program.

Thanks a lot.
 
Upvote 0
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.

The same sheet also shows the solution to "How to interrupt VBA execution through the ribbon UI" (http://www.mrexcel.com/forum/showthread.php?t=518631). See sheet InterruptUI.

Basically we do:
Code: 
[SIZE=2][FONT=Consolas][COLOR=black]Private Declare Sub CopyMemory Lib "kernel32" _[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas][COLOR=black]Sub StoreObjRef(obj As Object)[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]' Store an object reference[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Dim longObj As Long[/COLOR][/FONT][/SIZE]
[SIZE=2][COLOR=black][FONT=Consolas][B] longObj = ObjPtr(obj)[/B][/FONT][/COLOR][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Range("...") = longObj[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]End Sub[/COLOR][/FONT][/SIZE]
 
[SIZE=2][FONT=Consolas][COLOR=black]Function RetrieveObjRef() As Object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]' Retrieve the object reference[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Dim longObj As Long, obj as Object[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] longObj = Range("...")[/COLOR][/FONT][/SIZE]
[SIZE=2][COLOR=black][FONT=Consolas][B] CopyMemory obj, longObj, 4[/B][/FONT][/COLOR][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black] Set RetrieveObjRef = obj[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Consolas][COLOR=black]End Function[/COLOR][/FONT][/SIZE]
 
Upvote 0
Found this today. Fantastic, works a treat. Just wanted to say thank you rorya. You've saved me a great deal of time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Excel Custom Ribbon Drop Down Element - How to View Id, Index, Label for Each Item
Replies
0
Views
2K
CurtisD
C
I
  • Question Question
Overflow error in custom ribbon ID
Replies
4
Views
1K
imran88
I
alketrazz
  • Question Question
Enable/Disable Ribbon Controls Independantly
Replies
0
Views
9K
alketrazz
alketrazz
S
  • Question Question
Custom controls not showing in Add-ins ribbon tab and XML error
2
Replies
10
Views
8K
Shneuph
S
WernerGg
  • Question Question
How to interrupt VBA execution through the ribbon UI?
2
Replies
16
Views
8K
WernerGg
WernerGg

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top