Transposing Values In a Listbox-CodePudding

Good day,

I am trying to transpose the values added to my listbox via a query. Also does anyone have tips on how to get the column names on the Column Header. This is what the values in my listbox looks like:

Below is my code thus far:

    Dim conn As New ADODB.Connection
    Dim reCs As ADODB.Recordset
    Dim tarSheet As Worksheet
    Dim strbooK As String, strTar As String
    Dim arTar As Range
    
    strbooK = ThisWorkbook.Path & "\Excel_VBA.xlsm"

    
    If opTar.Value = True Then
 
        strTar = "SELECT TargetNumber as [Target Number] " & _
                 ",TargetName as [Target Name] " & _
                 ",TargetPrefix as [Target Nickname] " & _
                 ",Count(TargetNumber) as [Number of Events] " & _
                 "FROM [Master$] " & _
                 "GROUP BY TargetNumber, TargetName, TargetPrefix " & _
                 "HAVING TargetNumber = '" & txt_search.Value & "';"
                 
         Set conn = New ADODB.Connection
                    conn.ConnectionString = _
                    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strbooK & ";" & _
                    "Extended Properties='Excel 12.0 Xml;HDR=YES';"
                    
                    conn.Open
                    
                    Set reCs = New ADODB.Recordset
                    reCs.ActiveConnection = conn
                    reCs.Source = strTar
                    reCs.Open

        With lbx_reCs
            .BoundColumn = 1
            .ColumnCount = 4
            .ColumnHeads = True
            .TextAlign = fmTextAlignCenter
            .ColumnWidths = "136;136;136;136;"
            .MultiSelect = fmMultiSelectMulti
'            .RowSource = arTar.Address
            .List() = reCs.GetRows
        End With

'Releasing Objects
              reCs.Close
              conn.Close
        
    End If

Thank you

CodePudding user response：

As said in the titel you need no transpose of the values of the recordset, just use the column property of the listbox.

.Column = reCs.GetRows

For the other question you might look here. That seems not to be possible in case you have an array like in this case. On the other hand you also might want to look at this solution approach in the above mentioned post.