RibbonX Custom Dropdown Not Firing

[delaing]

I've created a custom Ribbon tab with several working Command buttons, etc. using Ken Puls info and Andy Pope's editor.

I have a Dropdown control that is very finicky and I'm not sure what is wrong. I modeled it after Ron deBruin's DD example setup and it works some of the time and others, doesn't respond at all.
Here is the code:

Code: Select all

Option Explicit

Dim ItemCount As Integer
Dim ListItemsRg As Range
Dim MySelectedItem As String

Public Sub ddFactbook_getItemCount(control As IRibbonControl, ByRef returnedVal)
    
    With Sheet1.Range("A2:A5")  ''Defines where the list of Factbook options is on Sheet1 of ThisWorkbook.
        Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
        ItemCount = ListItemsRg.Rows.Count
        returnedVal = ItemCount
    End With
    
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
''Same as RDB's "DDListItem" procedure in his example; he labelled differently.
Public Sub ddFactbook_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)

    returnedVal = ListItemsRg.Cells(index + 1).Value  ''index is 0-based, our list is 1-based so we add 1.
    
End Sub

''Because there is no direct way for getting a value of a dropdown we must use this
''Dropdown change handler to store the value somewhere if we want to use the value of it in a different macro.
''Called when a drop down item is selected.
Public Sub ddFactbook(control As IRibbonControl, id As String, index As Integer)
    MySelectedItem = ListItemsRg.Cells(index + 1).Value
    
    Select Case MySelectedItem
        Case "For 2017 work"
            Run "Personal.xlsb!factbook2017"
        Case "For 2016 work"
            Run "Personal.xlsb!factbook2016"
        Case "For 2015 work"
            Run "Personal.xlsb!factbook2015"
    End Select
    
End Sub

''Returns index of item to display.
Public Sub ddFactbook_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
    
End Sub

It is the code in the Case-Select of ddFactbook which is not being triggered. I've tested running the individual procedures outside of this Dropdown setup and they work just fine.

This is an image of the Tab showing the list of possible selections:

Factbook to use.png

Suggestions?

Thanks in advance,
Delain

[HansV]

I fear that we don't have many RibbonX experts on board...

[delaing]

Just wanted to toss it into the pond here and see if I would get a nibble.
I'm going to cross-post this over to Excel Forum.
https://www.excelforum.com/excel-progra ... ost4746540

Thank you,
Delain

[HansV]

Good luck, and let us know if you get a helpful reply...

[rory]

Are you saying that ddFactbook doesn't get called at all?

It would be helpful if you could post an actual workbook - it would make debugging a lot simpler.

[delaing]

Rory, didn't see your reply till now. Thanks, I will consider posting a wkbk; would have to strip my version down to get rid of other subs, etc. As I mentioned ddFactbook would run some of the time and not for other times. However, another Dropdown RibbonX Control I cloned from the Factbook setup works every time.

For the moment and in response to Hans' request for posting back my solution - I've changed the Control type on the Ribbonx setup. I failed to go to J-Walk's Power Programming book until yesterday and found some good examples for various Control types when customizing the Ribbon XML approach. So, I switched to a Gallery type Control and it is working perfectly, so far.

I created a Gallery Control and added in "Items" (by way of Ribbon Designer) to create a list of choices much like a Dropdown or Combobox provides. In fact, it behaves just like a Dropdown but takes up a bit less Ribbon real estate.

FBgallery.png

For the RibbonX, I am simply using an "onAction" Callback and here is the code for that, which is virtually the same as my original Dropdown Callback, but without the need for the extraneous subs (getSelectedItemIndex, getItemLabel, getItemCount).

Code: Select all

Sub FBSelected(control As IRibbonControl, selectedId As String, selectedIndex As Integer)
    ''Callback for Factbook Gallery type Control.
    ''This replaces the Dropdown I was using because it was too finicky.
    
    Select Case selectedId
        Case "galFB2017work"
            Run "Personal.xlsb!factbook2017"
        Case "galFB2016work"
            Run "Personal.xlsb!factbook2016"
        Case "galFB2015work"
            Run "Personal.xlsb!factbook2015"
    End Select
    
End Sub

It's frustrating that there seems to be very little help out on the Internet for solving Ribbonx conflicts. I am using Andy Pope's Designer, only, as I seemed to have too many errors creeping in when using the CustomUI Editor which just about everyone else seems to adhere to. When I say everyone else, I mean the book authors and various web site tutorials.

That's one of the hazards of me being a neophyte, yet acting like an expert.

Thank you,
Delain