asked on

Dynamic ActiveX ComboBox on Worksheet

HI,
I have an ActiveX ComboBox on a worksheet that fills with a dynamic array, but I cannot find any code to sort the data.
The list is all dates and I wanted to sort in descending order (newest date first).
The code I have which works is as follows

Private Sub ComboBox1_Change()
Dim daze As String
Dim WB1 As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WB1 = ActiveWorkbook
Set WS1 = WB1.Sheets("DSVmatrix")
Set WS2 = WB1.Sheets("Movements")
LRdata = WS1.Cells(Rows.Count, 1).End(xlUp).Row

'get the DSV dates for combobox1
    For x = 3 To LRdata
        If WS1.Cells(x, 9) <> "" And (InStr(daze, "|" & WS1.Cells(x, 9) & "|") = 0) Then
            If firsttime = True Then
                firsttime = False
                daze = "|" & daze & WS1.Cells(x, 9) & "|"
            Else
                daze = daze & WS1.Cells(x, 9) & "|"
            End If
        End If
    Next x

'build the array and add unique dates to combobox1
myArray = Split(daze, "|")
For Each cell In myArray
    If cell <> "" Then
         Me.ComboBox1.AddItem cell
    End If
Next cell

'how to Sort the combobox data here?

    ComboBox1.Value = Format(ComboBox1.Value, "dd-mmm-yyyy")
    Sheets("LookUpLists").Range("DSVdate").Value = Me.ComboBox1.Value
    Range("DSVfigures").Font.Color = vbBlack

End Sub

Open in new window

but I cannot fathom how to sort it. Any help as always is much appreciated

Martin Liss

This first creates an array from the data to be added to the combobox. The array is sorted and then the array data is added to the combobox.

Sub AddToCombo()
    Dim strArray(3) As String
    Dim lngIndex As Long
    
    strArray(0) = "this"
    strArray(1) = "is"
    strArray(2) = "a"
    strArray(3) = "test"
    
    QuickSort strArray, LBound(strArray), UBound(strArray)
    
    
    For lngIndex = LBound(strArray) To UBound(strArray)
        ComboBox1.AddItem strArray(lngIndex)
    Next

End Sub

Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2)

Do
While C(Low) < MidValue
Low = Low + 1
Wend

While C(High) > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub
Private Sub Swap(ByRef A As String, ByRef B As String)
Dim T As String

T = A
A = B
B = T
End Sub

Open in new window

Stephen Byrom

ASKER

looks tidy, but I cannot fathom how to include or get unique dates from the worksheet (which has over 3000 dates in a column) into the array

Martin Liss

Does your current code already add the unique dates to the combobox (but not sorted)? If so can you post that code, or better yet attach the workbook?

Stephen Byrom

ASKER

Hi Martin,
The code is in my first post of this topic

Stephen Byrom

ASKER

here's the book
Testing.xlsm

Martin Liss

Sorry that I missed the fact that you had posted the code. How do I get the combobox to appear?

Stephen Byrom

ASKER

Hmmm.
I forgot.
Maybe the sheet is hidden?
Sheets("Movements")

Stephen Byrom

ASKER

on the PasteSheet click the "View Archive" button

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Byrom

ASKER

Thanks for this Martin, but I cannot get the combobox to populate.
Remove the ListRange reference to the box in the properties window, and the box is empty.
I need the list range to be dynamic so I removed the reference

Stephen Byrom

ASKER

Thanks for your time and expertise

Martin Liss

So is the problem you mentioned in post ID: 41112184 no longer a problem?

Stephen Byrom

ASKER

Yes thanks....... all sorted

Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you
including these two new ones.
Creating your own Excel Formulas and doing the impossible
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014