Excel RibbonX Dropdown OnAction Error, can't be called

Question

I'm trying to add a dynamic dropdown to customised ribbon for an xlsm file. The idea of the dropdown is, it will generate a list from existing excel range. If user click any item of the dropdown list, it should take the user to the same named worksheet. It can generate the dropdown list correctly but when I try to click it, it says "Wrong number of arguments or invalid property assignment"

There are other customisde ribbon buttons on that tab and they works fine, so I think the problem may generates from xml code part or the onAction subprocess part. I'm also glad to provide other code segments in necessary.

Xml

<!--            Dynamic dropDown Group                 -->          
                <group id="tool5" label="Sheet DropDown" autoScale="true">          
                    <dropDown id="DropDown1" label= "Sheet Name" sizeString="WWWWWWWWWWWWW" supertip="Please select a sheet, add more sheetsname in Lookup sheet" imageMso="InsertExcelSpreadsheetMenu"
                              getItemCount="GetDropdownItemCount" getItemLabel="DropdownItemLabel" onAction="SubdropDownAction" />    
                </group>

VBA

I think GetDropdownItemCount and DropdownItemLabel is working fine since the dropdown list is properly generated.


Public Sub SubdropDownAction(control As IRibbonControl, index As Integer)
    
Dim SelectedSheet As String

    Select Case control.ID
        
        Case "DropDown1"
            SelectedSheet = Range("Range_DropdownsheetName").Cells(index + 1).Value
            ThisWorkbook.Sheets(SelectedSheet).Activate

        Case Else

    End Select
    
End Sub

It would help to show GetDropdownItemCount and DropdownItemLabel And which line produces the error - a breakpoint in SubdropDownAction and step through And a guess is, that Range("Range_DropdownsheetName") is not declared with workbook and worksheet this would only work, if the worksheet is active with the given range. Get all Values coming back in your SubdropDownAction — Red Hare, Apr 13 '23 at 13:42

score 2 · Answer 1 · answered Apr 13 '23 at 17:12

The dropDown control must have the following signature for the onAction callback:

C#: void OnAction(IRibbonControl control, string selectedId, int selectedIndex)

VBA: Sub OnAction(control As IRibbonControl, selectedId As String, selectedIndex As Integer)

C++: HRESULT OnAction([in] IRibbonControl *pControl, [in] BSTR *selectedId, [in] LONG cSelectedIndex)

Visual Basic: Sub OnAction(control As IRibbonControl, selectedId As String, selectedIndex As Integer)

So, your code should look like that:

Public Sub SubdropDownAction(control As IRibbonControl, selectedId As String, selectedIndex As Integer)
    
Dim SelectedSheet As String

    Select Case control.ID
        
        Case "DropDown1"
            SelectedSheet = Range("Range_DropdownsheetName").Cells(selectedIndex + 1).Value
            ThisWorkbook.Sheets(SelectedSheet).Activate

        Case Else

    End Select
    
End Sub

Hope it helps, but you can set a breakpoint and see what is wrong with your code further.

Thank you very much! I didn't found the difference about controls with button or dropdown. Your answer perfectly solved the problem! Thank you again! — Steinadler 177, Apr 14 '23 at 13:05

Excel RibbonX Dropdown OnAction Error, can't be called

Xml

VBA

1 Answers1