在VBA Excel中对ComboBox进行排序

I am trying to sort a ComboBox without success so far. I am using the classic code below found on internet:

到目前为止,我试图对ComboBox进行排序,但没有成功。我正在使用以下在互联网上找到的经典代码:

 Sub SortComboBox(ByRef oCB As ComboBox)
    Dim vItems As Variant
    Dim i As Long
    Dim j As Long
    Dim vTemp As Variant
    ' Put the items in a array
    vItems = oCB.List
    ' Sort the array
    For i = LBound(vItems, 1) To UBound(vItems, 1) - 1
        For j = i + 1 To UBound(vItems, 1)
            If vItems(i, 0) > vItems(j, 0) Then
                vTemp = vItems(i, 0)
                vItems(i, 0) = vItems(j, 0)
                vItems(j, 0) = vTemp
            End If
        Next j
    Next i
    ' Clear the ComboBox
    oCB.Clear
    ' Add the sorted array back to the ComboBox
    For i = LBound(vItems, 1) To UBound(vItems, 1)
        oCB.AddItem vItems(i, 0)
    Next i
End Sub

I am calling this Sub with this line:

我用这一行称这个Sub:

SortComboBox (Sheet1.cboSolvent)

The ComboBox 'cboSolvent' is on the Worksheet1 and is already filled from the worksheet2. I am calling the Sub 'SortComboBox' when the ComboBox 'cboSolvent' is already full but not sorted.

ComboBox的“cboSolvent”位于Worksheet1上,已经从工作表2中填充。当ComboBox的“cboSolvent”已经满但未排序时,我正在调用Sub'StonComboBox'。

I get the following error: 'object required', but I don't understand, because for me, cboSolvent is an object (a ComboBox object). Moreover, Sheet1.cboSolvent is highlighted with the message: Sheet1.cboSolvent = "Data". Another thing unclear for me as it should be Sheet1.cboSolvent.Text = "Data".

我得到以下错误:'对象需要',但我不明白,因为对我来说,cboSolvent是一个对象(一个ComboBox对象)。此外,Sheet1.cboSolvent突出显示消息:Sheet1.cboSolvent =“Data”。另一件事我不清楚,因为它应该是Sheet1.cboSolvent.Text =“数据”。

Any help will be appreciated to sort this issue of course, but also to understand the things I don't understand.

当然,任何帮助将被理解为对此问题进行排序,同时也理解我不理解的事情。

Thanks.

1 个解决方案

#1

Try to call it as (without brackets)

试着把它称为(没有括号)

SortComboBox Sheet1.cboSolvent

If you need to call the sub with brakets then use CALL keyword.

如果需要使用brakets调用sub,则使用CALL关键字。

Call SortComboBox(Sheet1.cbsolvent)

#1