时间戳

首页 > 解决方案 > Sort Combobox alphabetically vba excel

excel - Sort Combobox alphabetically vba excel

问题描述

I'm bulting a small programm with vba Excel. My Comboxbox is filled from an excel sheet. I want to build some controls like "add / delete items" to add other items to my Combobox that why i need to know the row number for each item on my combobox. my code is capable of doing this but i still don't know how to sort the items alphabetically before adding them to the combobox ( see my code below) can you help please ? thanks in advance 



Sub Fill_EightD_D1_CB1()

With EightD.EightD_D1_CB1
    .ColumnCount = 2        ' 2 colonnes
    .ColumnWidths = "-1;0"  ' dont une de masquée

End With

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
Dim LC As Long
Dim i As Long

LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

For i = 2 To LC

    If ws.Cells(i, 1) <> "" Then
    EightD.EightD_D1_CB1.AddItem ws.Cells(i, 1).Value
    EightD.EightD_D1_CB1.List(EightD.EightD_D1_CB1.ListCount - 1, 1) = Mid(ws.Cells(i, 1).Address(False, False), 2, 1) 

    End If
Next i

'show always the first element
EightD.EightD_D1_CB1.ListIndex = 0

'Bold Text EightD_D1_CB1
EightD.EightD_D1_CB1.Font.Bold = True

End Sub

标签： excelvbasortingcombobox

解决方案

尝试

Sub Fill_EightD_D1_CB1()

    With EightD.EightD_D1_CB1
        .ColumnCount = 2        ' 2 colonnes
        .ColumnWidths = "-1;0"  ' dont une de masquee

    End With
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
    Dim LC As Long
    Dim i As Long, r As Long, j As Long
    Dim vDB As Variant, vR(), vtemp(1 To 2)

    LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    vDB = ws.Range("a2", "a" & LC)
    r = UBound(vDB, 1)
    ReDim vR(1 To r, 1 To 2)
    For i = 1 To r
        vR(i, 1) = vDB(i, 1)
        vR(i, 2) = i + 1
    Next i
    For i = 1 To r
        For j = 1 To r
            If vR(i, 1) < vR(j, 1) Then
                vtemp(1) = vR(i, 1)
                vtemp(2) = vR(i, 2)
                vR(i, 1) = vR(j, 1)
                vR(i, 2) = vR(j, 2)
                vR(j, 1) = vtemp(1)
                vR(j, 2) = vtemp(2)
            End If
        Next j
    Next i
    EightD.EightD_D1_CB1.List = vR
    'show always the first element
    EightD.EightD_D1_CB1.ListIndex = 0
    'Bold Text EightD_D1_CB1
    EightD.EightD_D1_CB1.Font.Bold = True
End Sub

推荐阅读