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:
The relevant code I have for the populating the first listbox and temporarily demonstrating the item sorting on it is below:
Code:
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