英文:
Excel RibbonX Dropdown OnAction Error, can't be called
问题
I understand your request. Here is the translated content:
我试图为一个xlsm文件的定制功能区添加一个动态下拉菜单。下拉菜单的想法是，它将从现有的Excel范围生成一个列表。如果用户点击下拉列表的任何项，它应该将用户带到具有相同名称的工作表。它可以正确生成下拉菜单列表，但当我尝试点击它时，它显示“参数数量错误或无效的属性分配”。
在该选项卡上还有其他定制功能区按钮，它们工作正常，所以我认为问题可能出在`xml`代码部分或`onAction`子过程部分。如果需要，我也很乐意提供其他代码片段。
##### Xml
```xml
<!-- 动态下拉菜单组 -->
<group id="tool5" label="工作表下拉菜单" autoScale="true">
<dropDown id="DropDown1" label="工作表名称" sizeString="WWWWWWWWWWWWW" supertip="请选择一个工作表，在“Lookup”工作表中添加更多工作表名称" imageMso="InsertExcelSpreadsheetMenu"
getItemCount="GetDropdownItemCount" getItemLabel="DropdownItemLabel" onAction="SubdropDownAction" />
</group>
VBA
我认为GetDropdownItemCount和DropdownItemLabel工作正常，因为下拉菜单列表已正确生成。
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
<details>
<summary>英文:</summary>
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
</details>
# 答案1
**得分**: 2
The `dropDown` 控件必须具有以下 `onAction` 回调的签名：
```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)
因此，您的代码应该如下所示：
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
希望对您有所帮助，但您可以设置断点并查看进一步了解您的代码中有什么问题。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论