Custom Ribbon onLoad macro showing not available

indyman

New Member
Joined
Apr 14, 2021
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
I'm stumped. I have a custom ribbon created via the Office RibbonX Editor. I have 3 custom tabs with buttons, and textboxes. These have all been working for 2 months - thanks to the help of many people smarter than me on the web. But now when I open the workbook, I get this error - twice. "Cannot run the macro 'onLoad'. The macro may not be available in this workbook or all macros may be disabled." The custom ribbon loads, but the callbacks also show as unavailable. I have not touched my ribbon module since I got it all working 2 months ago. Anyone have a clue as to what's up?

So I created a new WB and stripped it down to the basics, and it does the same thing. Here is the UI XML:

XML:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="onLoad">
   <ribbon>
    <tabs>
      <tab id="MyTab" label="Custom" insertAfterMso="TabHome" >
        <group id="grpInputForms" label="Data Entry and Edit">
          <button id="btnIncomes" label="Incomes" size="large"
            onAction="Inc_Entry" image="Income" />
          <button id="btnExpenses" label="Expenses" size="large"
            onAction="Exp_Entry" image="Expense" />           
        </group>           
      </tab>
    </tabs>
  </ribbon>
</customUI>

And here is my ribbon code.
VBA Code:
Option Explicit
Public RibbonTextBox As String
Public myRibbon As IRibbonUI
'Public MyTag As String

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

#If VBA7 Then
Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If
        Dim objRibbon As Object
        CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
        Set GetRibbon = objRibbon
        Set objRibbon = Nothing
End Function

'Callback for customUI.onLoad
Sub onLoad(ribbon As IRibbonUI)
    Set myRibbon = ribbon
       ' Store pointer to IRibbonUI
    ThisWorkbook.Sheets("Sheet1").Range("C27").Value = ObjPtr(myRibbon)

End Sub

'Callback for Income onAction
Sub Inc_Entry(control As IRibbonControl)
        MsgBox "Inc"
    End If
End Sub

'Callback for Expenses onAction
Sub Exp_Entry(control As IRibbonControl)
        MsgBox "Exp"
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
First I copied your XML into the CustomUI part. Then I copied your VBA code into a regular/standard module. Then I removed End If from the Inc_Entry.procedure. And then I saved and closed the wokbook. When I opened it, I didn't get any errors and I was able to run both macros successfully.
 
Solution

indyman

New Member
Joined
Apr 14, 2021
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
First I copied your XML into the CustomUI part. Then I copied your VBA code into a regular/standard module. Then I removed End If from the Inc_Entry.procedure. And then I saved and closed the wokbook. When I opened it, I didn't get any errors and I was able to run both macros successfully.
@Domenic Thanks for seeing my End If error. Unfortunately that was a miss on my part when I stripped this down for troubleshooting. And indeed it works for me when I remove it. But the bigger question was my non-stripped code causing an issue. But what you just taught me is that any compile error in ribbon code can cause this error. So I just added subs back into the module one by one until I got the error. Quite time consuming.

I had one variable not defined in one sub. During some code cleanup I had noticed Option Explicit was not on this module. I added it but failed to compile it. So I guess I did make a change to the ribbon code and forgot about it. Lesson learned.

Thanks for your help.
 

Forum statistics

Threads
1,141,707
Messages
5,707,980
Members
421,539
Latest member
zuniBM

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
Top