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
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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 ByromWarehouse/ShippingAuthor Commented:
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 LissOlder than dirtCommented:
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?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Stephen ByromWarehouse/ShippingAuthor Commented:
Hi Martin,
The code is in my first post of this topic
Stephen ByromWarehouse/ShippingAuthor Commented:
here's the book
Testing.xlsm
Martin LissOlder than dirtCommented:
Sorry that I missed the fact that you had posted the code. How do I get the combobox to appear?
Stephen ByromWarehouse/ShippingAuthor Commented:
Hmmm.
I forgot.
Maybe the sheet is hidden?
Sheets("Movements")
Stephen ByromWarehouse/ShippingAuthor Commented:
on the PasteSheet click the "View Archive" button
Martin LissOlder than dirtCommented:
The sheet was hidden. Take a look at this workbook. In order for the dates to be sorted they need to be in year/month/day format.
28773939.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stephen ByromWarehouse/ShippingAuthor Commented:
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 ByromWarehouse/ShippingAuthor Commented:
Thanks for your time and expertise
Martin LissOlder than dirtCommented:
So is the problem you mentioned in post ID: 41112184 no longer a problem?
Stephen ByromWarehouse/ShippingAuthor Commented:
Yes thanks....... all sorted
Martin LissOlder than dirtCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.