VBA Sorting a 2D Array graphically using a listbox & command buttons on a form

[blothian]

Currently via VBA I am opening an xlsx file, detecting the names of any worksheets and displaying them in a listbox(single item select only). After selecting an listbox item a specific range of cells is put into a 2D array and my macro then creates individual text elements within a CAD program from each of the array values. The difference between the source and output data is that it is transposed in the CAD program so the column headers are to the left of the table and the data to the right of it but the text is still horizontal. I can create the text elements looping through the array as you normally would however I am looking to add functionality that will allow the user to choose the order that the columns will be created within the CAD program. To do this I envisioned creating a variant array from first row in the array the selected worksheet (which act as column headers), display these values in a another listbox on the form and controlling its item positions via 2 command buttons (1 to move the selected item up, the other to move it down). This much I can do already however I am unsure how to tie the sorting of the listbox values to actual sorting of the array and which sorting technique to use; I gather from quick searches that there are several (bubble, insertion, quick etc..)

The relevant code I have for the populating the first listbox and temporarily demonstrating the item sorting on it is below:

Public Sub ListSheets(ByVal file_name As String)


    On Error Resume Next


    If IsExcelRunning = False Then
        Set oXlApp = CreateObject("Excel.Application")
    End If


    Set oXlWkBk = Workbooks.Open(sWorkbookFullName, , True)
    For Each oXlWkSht In oXlApp.Worksheets
        FrmPWayLevel.LB_TrackName.AddItem oXlWkSht.Name
    Next
End Sub

Private Sub LB_TrackName_Click()
[I]
....non-relevant code above omitted for clarity...[/I]


    With LB_TrackName
        If .ListIndex = 0 Then
            MoveUp.Enabled = False
        Else
            MoveUp.Enabled = True
        End If
        If .ListIndex = .ListCount - 1 Then
            MoveDown.Enabled = False
        Else
            MoveDown.Enabled = True
        End If
    End With
End Sub


Private Sub MoveDown_Click()
    Dim Item                                 As Integer
    Dim Temp                                 As String


    With FrmPWayLevel.LB_TrackName
        Item = .ListIndex
        Temp = .List(Item + 1)
        .List(Item + 1) = .List(Item)
        .List(Item) = Temp
        .ListIndex = .ListIndex + 1
    End With
End Sub


Private Sub MoveUp_Click()
    Dim Item                                 As Integer
    Dim Temp                                 As String
    With FrmPWayLevel.LB_TrackName
        Item = .ListIndex
        Temp = .List(Item - 1)
        .List(Item - 1) = .List(Item)
        .List(Item) = Temp
        .ListIndex = .ListIndex - 1
    End With


End Sub


Private Sub UserForm_Initialize()
    MoveUp.Enabled = False
    MoveDown.Enabled = False
End Sub

 

[blothian]

btw, if anything needs explained clearer, just ask.

 

[blothian]

I've been getting some assistance elsewhere but I still haven't fully resolved the issue. The current strategy is to add a 2nd column to the listbox and populate it with the same initial values in column 1, duplicate the existing array and after sorting the listbox items in column 1 to the preferred order, the order of the columns in the 2nd array is meant to change to match it. I have some sample code which I was provided with and I have amended the variable names to suit my project however it appears incomplete and is giving a type mismatch error:

Private Sub SortArray()
    Dim R                                    As Integer
    Dim C                                    As Integer


    ReDim ArrayOutput(0)
    ArrayOutput = ArraySource


    For R = LBound(ArraySource, 1) To UBound(ArraySource, 1)
        For C = LBound(ArraySource, 2) To UBound(ArraySource, 2)
            ArrayOutput(R, C) = ArraySource(R, FrmPWayLevel.LB_WsHeaders.List(C - 1, 1))
            Debug.Print "Row No." & R & "/Column No." & C & " =" & ArrayOutput(R, C)
        Next C
    Next R
End Sub

ArrayOutput(R, C) = ArraySource(R, FrmPWayLevel.LB_WsHeaders.List(C - 1, 1)) is what errors because FrmPWayLevel.LB_WsHeaders.List(C - 1, 1) returns the value of that entry and not a variant number so I don't quite know how this code is meant to reorder the 2nd array.