Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Build Excel Add-in > Ribbon Callbacks
Deze pagina in het Nederlands

Building an Add-in for Excel, ribbon callbacks

Ribbon load

In order to be able to tell Excel it needs to update the ribbon we need a pointer to the Ribbon object. That pointer is passed to us in the onLoad callback. So lets add a module level object variable and assign the Ribbon object to it:

Option Explicit

Dim moRibbon As IRibbonUI

Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI)
    Set moRibbon = ribbon
End Sub

There. VBA now remembers the ribbon object so we can ask it to refresh from our code. When the Excel file is opened and you enable macro's, onLoad is the first callback that gets called.

Populating the dropdown with sheet tab names

The next thing that happens is that the dropdown requests the number of items it needs to load. This is done in the rxJKPSheetToolsbtnSheets getItemCount callback:

'Callback for rxJKPSheetToolsbtnSheets getItemCount
Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal)
    Dim lCt As Long
    Dim oSh As Object
    For Each oSh In Sheets
        If oSh.Visible = xlSheetVisible Then lCt = lCt + 1
    Next
    returnedVal = lCt
End Sub

The routine simply counts the number of visible sheets in the active workbook and passes that number to the returnedVal variable which is passed back to Excel.

Then the next callback is triggered, which fetches the actual sheet names:

Public Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, Index As Integer, ByRef returnedVal)
    Dim lCt As Long
    Dim oSh As Object
    For Each oSh In Sheets
        If oSh.Visible = xlSheetVisible Then lCt = lCt + 1
        If lCt = Index + 1 Then
            returnedVal = oSh.Name
            Exit For
        End If
    Next
End Sub

This callback is called as many times as the number of sheets you passed in the previous callback. Silly enough, the Index starts at zero, so you have to pay attention what you return to it. So each call to the sub asks for one sheet name.

How I wish the designers of (Excel) VBA had been smart enough to decide up front whether all object collections, lists and arrays would start at a zero index or at a 1 index. But they didn't so we're stuck with the inconsitencies resulting from that.

When the dropdown is populated, we want it to show the name of the active sheet. Which is handled by this callback:

'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex
Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = ActiveSheet.Index - 1
End Sub

And finally, if we select a sheet from the dropdown, we want it to take us there:

Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, Index As Integer)
    Dim lCt As Long
    Dim oSh As Object
    For Each oSh In Sheets
        If oSh.Visible = xlSheetVisible Then lCt = lCt + 1
        If lCt = Index + 1 Then
            oSh.Activate
            Exit Sub
        End If
    Next
End Sub

Remember I said the dropdown index starts at zero? Well, Excel's Sheet index starts at 1.

Having the button update the TOC

O wait, we're not done yet; the button responds to nothing. We have an empty callback for that so lets have that call our macro we designed at the beginning:

Sub rxJKPSheetToolsbtnInsertTOC(control As IRibbonControl)
    UpdateTOC
End Sub

So that wasn't very hard, was it?

Next: Making the ribbon respond to changing worksheets


 


Comments

Showing last 8 comments of 9 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (5/11/2015 9:09:06 AM)

Hi Doug,

Thanks. I expect you're referring to the id attributes in the RibbonX code?

 


Comment by: Doug Glancy (5/12/2015 3:28:08 AM)

No, I'm just talking about the name of the global ribbon variable, e.g., your "moribbon". I haven't really tested to confirm the problem. I just know that when I started giving each ribbon its own variable name the crashing stopped.

 


Comment by: Jan Karel Pieterse (5/12/2015 8:09:13 AM)

Hi Doug,

That is really odd. I have never seen conflicts between identical variable names in different VBA projects in Excel.

Perhaps this is more about not having unique callback subroutine names?

I have seen crashes when using the trick to recapture the ribbon instance using win API's when the pointer address wasn't actually stored at initialisation so the wrong address was used.

 


Comment by: Doug Glancy (5/13/2015 4:52:17 AM)

It could be a callback name issue as easily as a variable name issue, since I changed the callback names to match the new, unique, variable names.

I do use the API trick but it doesn't seem like that's the issue.

Like you, I'd never seen a variable name conflict, but the ribbon does seem to create some unique circumstances. For instance I take all the code that would normally be in the addin's Workbook_Open event and put it in the Ribbon_OnLoad event. Otherwise I get another type of conflict, as described in one of my first posts: http://yoursumbuddy.com/a-workbook-hooker-with-no-ribbon-related-fatalities/.

 


Comment by: Jan Karel Pieterse (5/13/2015 1:16:15 PM)

Hi Doug,

Well, I've seen the API trick cause havoc. The onload callback name must also be unique to any other ribbonX loaded in Excel at that time, otherwise it'll call the wrong routine and the pointer you stored is outdated.

 


Comment by: Doug Glancy (5/14/2015 5:11:15 PM)

Jan Karel, sounds like you've identified the issue. I changed both the variable and callback names at the same time and I do use the pointer storage trick.

Thanks for clearing that up!

 


Comment by: David Barton (11/22/2015 4:13:00 PM)

Hi
This is a very nicely explanation of using call backs. Has helped me a lot and I will use it in the future.

Only one comment. I think that the getSelectedItem Index needs to be modified to that the correct sheet name is display when there are hidden sheets in the workbook. Perhaps something like:

     Dim lCount As Long
     Dim obj As Object
    
     For Each obj In Sheets
    
         If obj.visible = xlSheetVisible Then lCount = lCount + 1
        
         If obj.Name = ActiveSheet.Name Then
             returnedVal = lCount - 1
             Exit Sub
         End If
        
     Next


Many thanks for creating and maintaining this site

Thanks
David

 


Comment by: Jan Karel Pieterse (11/23/2015 5:12:50 PM)

Hi David,

Thanks!

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].