Consulting

Results 1 to 9 of 9

Thread: Ribbon Object

  1. #1

    Ribbon Object

    Hi everybody,

    i'm makeing a simple add in in excel 2007 with a customized tab.

    actually the ribbon is very user friendly once you get customed with it...still it is a bit longer to program than the usual menu we had before...by the way...

    i'm having this problem: i use an editbox to get some parameter and then perform an action with that... after the routin the editbox is invalidate...

    but if something goes wrong the public object myribbon (initialized in onload routine of the customUI), goes away (I can see that is value is nothing)...

    how can i prevent it to vanish? shall i take more care of the error handling? in wich way? is there a way reinitialize the public object myribbon?

    I don't know if i've been good enough in explaining my problem...ask me if not.

    many thanks

    Antonio

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,829
    Location
    Good question, often asked, never solved, but I hear Office 2010 is better

    1. There's a Office Ribbon forum here also

    2. http://www.vbaexpress.com/forum/showthread.php?t=22053

    Paul

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,293
    Location
    No, it is just as bad in Office 2010. You have more control over the ribbon, but once the ribbon object goes, you have to reload that tab (which means the workbook).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    this is a very bad news..by the way i'll check the right forum...sorry i didn't notice the ribbon section...

    if there is a moderator could you please move the topic there?

    thanks

    Antonio

  5. #5
    Quote Originally Posted by Paul_Hossler
    the backup object is a very good idea! i'll try it hoping that excel cancel out just the current ribbon object and not every public object.

    thanks

    if anyone else has suggestion i'll wait few days than i'll mark as solved

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,293
    Location
    Quote Originally Posted by AntonioZZZ
    this is a very bad news..by the way i'll check the right forum...sorry i didn't notice the ribbon section...

    if there is a moderator could you please move the topic there?

    thanks

    Antonio
    Moved as requested.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    never used office 2007

  8. #8
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    This is the way I've started referencing it, based on internet research. Obviously where you actually put your memory reference is flexible. I happened to choose the primary header of my vba addin, but it could go anywhere. If in Excel, you could put it on a hidden sheet. In Powerpoint you could put it in some hidden object, I suppose. The primary help with this was WernerGg at the Code Cage Forums, although their application was in Excel.

    Whoops, one clarification. I utilize custom conversion functions (so I have "fCStr" instead of "CStr") which basically just error trap any unexpected errors to return the default value.

    So my fCStr(null) will return "", whereas CStr(null) would return an error. Remove the "f" from those two lines of code and the below will work, at least most of the time. Or write your own functions to duplicate the above.

    Hope this helps anyone who was looking for it. This functionality was invaluable during my development in 2010, so I thought I would share.

    'public variable for our ribbon (for the callbacks)
    Public pub_myRibbon As IRibbonUI
    'required to store pointers to the ribbon
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                        (destination As Any, source As Any, ByVal length As Long)
    
    '----------------------------------------------------------------------------------------------
    'The onload event-- MS says not to put any sort of dialog pop ups during this
    '----------------------------------------------------------------------------------------------
    Public Sub onLoad(Ribbon As IRibbonUI)
      Dim lRibbonPointer As Long
      
      'load our public variable
      Set pub_myRibbon = Ribbon
    
      '*** Store a memory reference to our ribbon object, in case the VDE loses scope
      'get our long pointer reference, using the objptr function
      lRibbonPointer = ObjPtr(Ribbon)
      'put in the header, so we can store notes in the main body of the project
      ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text = fCStr(lRibbonPointer)
      'mark this document as saved, so this never triggers a "do you want to save this project?"
      ThisDocument.Saved = True
    End Sub
    '----------------------------------------------------------------------------------------------
    'allow us to reference the ribbon, regardless if the public variable has been lost
    'utilizes the CopyMemory library
    '----------------------------------------------------------------------------------------------
    Public Function myRibbon() As IRibbonUI
      Dim oRibbon As Object
      Dim lRibbonPointer As Long
      
      'if we've lost our scope, recreate it from our stored pointer
      If pub_myRibbon Is Nothing Then
        'get our pointer
        lRibbonPointer = fCLng(ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text)
        'this reloads it from memory
        CopyMemory oRibbon, lRibbonPointer, 4
        'and restores our public object
        Set pub_myRibbon = oRibbon
      End If
      'return our value
      Set myRibbon = pub_myRibbon
    End Function

  9. #9

    for 64 bits add this

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


    Quote Originally Posted by Frosty View Post
    This is the way I've started referencing it, based on internet research. Obviously where you actually put your memory reference is flexible. I happened to choose the primary header of my vba addin, but it could go anywhere. If in Excel, you could put it on a hidden sheet. In Powerpoint you could put it in some hidden object, I suppose. The primary help with this was WernerGg at the Code Cage Forums, although their application was in Excel.

    Whoops, one clarification. I utilize custom conversion functions (so I have "fCStr" instead of "CStr") which basically just error trap any unexpected errors to return the default value.

    So my fCStr(null) will return "", whereas CStr(null) would return an error. Remove the "f" from those two lines of code and the below will work, at least most of the time. Or write your own functions to duplicate the above.

    Hope this helps anyone who was looking for it. This functionality was invaluable during my development in 2010, so I thought I would share.

    'public variable for our ribbon (for the callbacks)
    Public pub_myRibbon As IRibbonUI
    'required to store pointers to the ribbon
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                        (destination As Any, source As Any, ByVal length As Long)
    
    '----------------------------------------------------------------------------------------------
    'The onload event-- MS says not to put any sort of dialog pop ups during this
    '----------------------------------------------------------------------------------------------
    Public Sub onLoad(Ribbon As IRibbonUI)
      Dim lRibbonPointer As Long
      
      'load our public variable
      Set pub_myRibbon = Ribbon
    
      '*** Store a memory reference to our ribbon object, in case the VDE loses scope
      'get our long pointer reference, using the objptr function
      lRibbonPointer = ObjPtr(Ribbon)
      'put in the header, so we can store notes in the main body of the project
      ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text = fCStr(lRibbonPointer)
      'mark this document as saved, so this never triggers a "do you want to save this project?"
      ThisDocument.Saved = True
    End Sub
    '----------------------------------------------------------------------------------------------
    'allow us to reference the ribbon, regardless if the public variable has been lost
    'utilizes the CopyMemory library
    '----------------------------------------------------------------------------------------------
    Public Function myRibbon() As IRibbonUI
      Dim oRibbon As Object
      Dim lRibbonPointer As Long
      
      'if we've lost our scope, recreate it from our stored pointer
      If pub_myRibbon Is Nothing Then
        'get our pointer
        lRibbonPointer = fCLng(ThisDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.text)
        'this reloads it from memory
        CopyMemory oRibbon, lRibbonPointer, 4
        'and restores our public object
        Set pub_myRibbon = oRibbon
      End If
      'return our value
      Set myRibbon = pub_myRibbon
    End Function

Posting Permissions

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