Custom Ribbon fails on Invalidate

[CurtisD]

Hello,

I am learning how to create and work with a custom ribbon in Excel 2019, but clearly have much to learn. I am running Windows 10.

I created the XML and callback functions from examples I've seen on various Excel websites. I am not trying to create an add-in.
When I open the Excel workbook the following error appears:

Error 91 - Object variable or with block variable not set

The line that is highlighted is:

testRibbon.Invalidate

I can't see the code in the Invalidate method since that is a built in method.

I'm hoping someone can help with this problem. I have a few thoughts:

- Perhaps the schema 2009/07 should be replaced with another one?
- Perhaps I need to set a reference to a library to get this to work?

Thank you.

Below is the XML and the callback functions.

XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="Tab1" label="Jobs on Hiatus">
<group id="Group1" label="Jobs on Hiatus">
<dropDown
id="DropDown"
label="Select Job To&#xA;Return from Hiatus"
getItemCount="DropDown_getItemCount"
getItemID="DropDown_getItemID"
getItemLabel="DropDown_getItemLabel"
getSelectedItemID="DropDown_getSelectedItemID"
onAction="DropDown_onAction" />
</group>
</tab>
/tabs>
</ribbon>
</customUI>

Callback functions

Option Explicit

'testRibbon is a variable which contains the Ribbon
Public testRibbon As IRibbonUI

'Callback for customUI.onLoad
Public Sub testRibbon_onLoad(ribbon As IRibbonUI)
Set testRibbon = ribbon
End Sub

'Callback for DropDown getItemCount
Public Sub DropDown_getItemCount(control As IRibbonControl, ByRef returnedVal)
returnedVal = numberOfRowsOnSheet("Hiatus")
End Sub

'Callback for DropDown getItemLabel
Public Sub DropDown_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
returnedVal = Worksheets("Hiatus").Cells(index + 1, 18)
End Sub

'Callback for DropDown onAction
Public Sub DropDown_onAction(control As IRibbonControl, id As String, index As Integer)
Call doHiatusJobReturn
End Sub

Public Sub DropDown_getItemID(control As IRibbonControl, index As Integer, ByRef id)
'This Callback will set the id for each item created.

id = Format(index + 1)

End Sub

Public Sub DropDown_getSelectedItemID(control As IRibbonControl, ByRef id)
'This Callback will change the drop-down to be set to a specific the id.

id = "1"

End Sub

Public Sub updateRibbon()
'This is a standard procedure, not a Callback. It is triggered by the button.
'It invalidates the Ribbon, which causes it to re-load.

On Error Resume Next
testRibbon.Invalidate
On Error GoTo 0

End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Custom Ribbon Fails When Invalidate Method Called
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

Your XML isn't calling the onLoad at all. You need:

<customUI onLoad="testRibbon_onLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">

 

[CurtisD]

Your XML isn't calling the onLoad at all. You need:

<customUI onLoad="testRibbon_onLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">

Thank you, Rory. That worked. Your help is appreciated!