Results 1 to 2 of 2

Thread: Stepping any macro causes my ribbon bar to fail

  1. #1

    Stepping any macro causes my ribbon bar to fail

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to this forum and thanks to all who read this post.

    I have created a custom ribbon for an Excel spreadsheet and have the following problem when I break/step any macro in VBA:

    If I put in a break point in a macro and then abort the macro when the execution reaches that point my ribbon bar no longer works. As I mouse over I get all sorts of errors stating that variables are out of range etc. I have to restart Excel and then the ribbon bar works fine. Here are the steps I do to create this problem:

    1. create a custom ribbon complete with callbacks and macros that are called for each callback
    2. open the excel file and access the vba editor
    3. open any macro and put in a break point
    4. run the macro
    5. when the macro gets to the break point, stop the macro.
    6. If I return to Excel, my ribbon bar no longer works.

    The macros I try to break and then abort have nothing to do with the custom ribbon and aren't even called by the ribbon.

    Not a huge problem but a little annoying that every time I try to troubleshoot a macro I have to restart Excel.


    1. I am running Windows XP SP3
    2. I am using Office 2010 but this happens in Office 2007 as well

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    This is, unfortunately, to be expected. Every time there is an error, the RibbonX object goes out of scope, and we can't get it back. The only way to do so is to re-open the workbook.

    Rory Archibald did come up with a method to store a pointer to the Ribbon object though, which looks like this:

    Public Declare Sub CopyMemory Lib "kernel32" Alias _
        "RtlMoveMemory" (destination As Any, source As Any, _
        ByVal length As Long)
    Public Sub ribbonLoaded(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
       Set objRibbon = Nothing
    End Function
    You'd then use the following code to restablish a link to the ribbon when it dies:
    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
          Set guiRibbon = GetRibbon(CLng(Tabelle2.Range("A1").Value))
            ' 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
    Rory's original post can be found here
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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