How to preserve or regain the Id of my custom ribbon UI?
Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 57

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

  1. #11
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,614
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

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

    You're welcome!

  2. #12
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,658
    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?



    Just exactly, perfectly what I was looking for.

    Thanx Rory!
    (XL2010 on Windows 7 Professional SP 2)

  3. #13

    Join Date
    Jun 2011
    Posts
    2
    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?

    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:
    Code:
    ''' Write the pointer 
    Application.ExecuteExcel4Macro   _
        "SET.NAME(""RibbonXPointer"",lngRibPtr)"
      
    ''' Read the pointer
    lngRibPtr=Application.ExecuteExcel4Macro("RibbonXPointer")

    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

  4. #14
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,658
    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 sebastienm View Post
    Hi Rory,


    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?)
    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.
    (XL2010 on Windows 7 Professional SP 2)

  5. #15

    Join Date
    Jun 2011
    Posts
    2
    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?

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

  6. #16
    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?

    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:

    Code:
     
     
    ' -------------- 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

  7. #17
    New Member
    Join Date
    Oct 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?

    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

  8. #18
    Board Regular
    Join Date
    Jan 2010
    Posts
    69
    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?

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

  9. #19
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,658
    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 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:

    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
    Code:
    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
    Code:
    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?
    (XL2010 on Windows 7 Professional SP 2)

  10. #20
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    31,614
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    2 Thread(s)

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

    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?

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
  •