Listbox in VBA forms

Michael Laursen

Listbox in VBA forms

Hi fellow COE members.
I have been trying to get data from excel and into the listbox using the "RowSource" function with no luck.
It works with "List" but then i can't have header,
Is it correct that it's not working..? Or am i doing something wrong..?

Thx for the help.. Mike


 Sub Refresh_Data(path As String)

Dim Excel As Object

    On Error Resume Next

    'if Excel is already running, then get the Excel object
    Set Excel = GetObject(, "Excel.Application")
 

    If Err.Number <> 0 Then
    
       'If Excel is not already running, then create a new session of Excel
        Set Excel = CreateObject("Excel.Application")

        Excel.Visible = False

     End If

    Dim wb As workbook
    Set wb = Excel.workbooks.Open(path)
        
    Dim sh As Worksheet
    Set sh = wb.Sheets("Data")
    
    Debug.Print ActiveSheet.Name
    
    
    Dim last_Row As Long
    last_Row = Excel.Application.WorksheetFunction.CountA(sh.Range("A:A"))
    
    Debug.Print last_Row
    Dim rg As Range
    Set rg = sh.Range("A2:H3")
    

    With Me.ListBox1
        
        .ColumnHeads = True
        .ColumnCount = 8
        .ColumnWidths = "60,100,120,40,100,120,80"
        '.List = sh.Range("A1:H4").Value
       .RowSource = "Data!A2:H3"


    End With
    
.RowSource = "Data!A2:H3"
        

End Sub


 

Little Cthulhu

RE: Listbox in VBA forms
(in response to Michael Laursen)

First hit in Google:

 

If your RowSource points to a range of cells, the column headings in a multi-column listbox are taken from the cells immediately above the RowSource.

Is this your case?

 

 

Michael Laursen

RE: Listbox in VBA forms
(in response to Little Cthulhu)

Hi Little Cthulhu, 

i hope you’re doing okay..

i see I the example that I have two .RowSource but this I know. 
The problem is no data at all. If I use the out commented line with the list.. the. It works fine. Maybe it’s because I’m using the xlsb ( binary ) that’s the only thing I haven’t tried to change.