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

Page 1 of 6 123 ... LastLast
Results 1 to 10 of 57

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

  1. #1
    New Member WernerGg's Avatar
    Join Date
    Oct 2010
    Location
    Stuttgart, Germany.
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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!

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,518
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by WernerGg View Post
    Is there really no possibility to regain that guiRibbon value at runtime or store it somewhere else where it is save from being reset?
    No, I'm afraid not.

  3. #3
    New Member WernerGg's Avatar
    Join Date
    Oct 2010
    Location
    Stuttgart, Germany.
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by rorya View Post
    No, I'm afraid not.
    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?

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,518
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    New Member WernerGg's Avatar
    Join Date
    Oct 2010
    Location
    Stuttgart, Germany.
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by rorya View Post
    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.
    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?

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,518
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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...

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,518
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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
       Set objRibbon = Nothing
    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 by RoryA; May 22nd, 2013 at 09:50 AM. Reason: added clean up code to GetRibbon function

  8. #8
    New Member WernerGg's Avatar
    Join Date
    Oct 2010
    Location
    Stuttgart, Germany.
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by rorya View Post
    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:
    Whow! That's exactly what we need. I will build that into my program.

    Thanks a lot.

  9. #9
    New Member WernerGg's Avatar
    Join Date
    Oct 2010
    Location
    Stuttgart, Germany.
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Private Declare Sub CopyMemory Lib "kernel32" _
     Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)
     
    Sub StoreObjRef(obj As Object)
    ' Store an object reference
     Dim longObj As Long
     longObj = ObjPtr(obj)
     Range("...") = longObj
    End Sub
     
    Function RetrieveObjRef() As Object
    ' Retrieve the object reference
     Dim longObj As Long, obj as Object
     longObj = Range("...")
     CopyMemory obj, longObj, 4
     Set RetrieveObjRef = obj
    End Function

  10. #10
    New Member
    Join Date
    Jan 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Found this today. Fantastic, works a treat. Just wanted to say thank you rorya. You've saved me a great deal of time.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com