Adding Listbox dynamically to Excel row in VBa from defined List

[drunkenneo]

Hi all,

I have coded for the dynamically adding of listbox in Excel. Please help me in this...

tching from PO Header
    stSQL = "SELECT OrderRefNo, BaseQty, BundleQty, RedemptionQuantity," & _
            "BrownBoxQty, InputUnitPrice, UnitPrice, a.TotalAmount, a.CurrencyFrom, " & _
            "a.CurrencyTo, a.ExchangeRate, VariationSku, a.POSentDate " & _
            "FROM [Fact].[Podetail] a JOIN [Fact].[PoHeader] b " & _
            "ON a.PoSID = b.PoSID " & _
            "WHERE PoRefNo='" & PoNum & "'"

 
 Set Rs = Conn.Execute(stSQL)
    
    iRow = 29
     If Rs.RecordCount <> 0 Then
        Rs.MoveFirst
    Else
        Rs.Close
        Conn.Close
        Set Conn = Nothing
        Set Rs = Nothing
        Exit Sub
    End If
        
    nbFields = Rs.Fields.Count
    
 

 
    'Rows.count returns the last row of the worksheet (which in Excel 2007 is 1,048,576); Cells(Rows.count, "B") returns the cell B1048576, ie. last cell in column B, and the code starts from this cell moving upwards; the code is bascially executing Range("B1048576").End(xlUp), and Range("B1048576").End(xlUp).Row finally returns the last row number.
    lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    'Blanking the rows
   
         Range("B29:XX" & lastRow).ClearContents
    
        'Assiging a List Box to "Action" column, It uses the datafrom Column XES
        lastActionListRow = ActiveSheet.Cells(Rows.Count, "XES").End(xlUp).Row
    
        'Assiging a List Box to "Action" column, It uses the datafrom Column XES
        lastNameListRow = ActiveSheet.Cells(Rows.Count, "XEU").End(xlUp).Row
    

      

    
       iRow = 29 ' Set it for your Excel Sheet Starting Row
    Do While Not Rs.EOF  'to traverse till last record
    'This for loop will display the values
    'of all column values in one Single record
        iCol = 2 'Every next Record Should start from Starting Column
        For j = 0 To Rs.Fields.Count - 1
          Worksheets("Summary").Cells(iRow, iCol).Value = Rs(j).Value
          iCol = iCol + 1 'move to next column in same row
        Next
        Worksheets("Summary").Cells(29, iCol + 1) = ListBox1.RowSource("Sheet1!XES1:XES" & lastActionListRow)
        Worksheets("Summary").Cells(29, iCol + 2) = ListBox1.RowSource("Sheet1!XEU1:XEU" & lastNameListRow)
        
        Rs.MoveNext 'This moves the loop to next record from the record set
        iRow = iRow + 1 'Move to next row in Excel
    Loop
 
    Rs.Close