locked
ListBox ColumnHeads RRS feed

  • Question

  • Hi ... 

    I m a totally newbie in this.... so sorry if my questions are not clear enough... 

    i have a multi column list box which is getting the data from a sheet...  i kept on getting runtime error 380... i m sure the prob is where the RowSource is (i tried commenting that row out and run it and everything else is fine, just that the space where is ColumnHead should be is blank) ... 

    thanks in advance

    my code is as follow :

        Dim LR As Long
        Dim sh As Worksheet
            
        LR = ThisWorkbook.Worksheets("Employee Data").Cells(Rows.Count, 1).End(xlUp).Row
        If LR = 1 Then LR = 2
        Set sh = ThisWorkbook.Sheets("Employee Data")

        For i = 2 To LR
        
        With Me.ListBEmpList
        
            .ColumnHeads = True
            .ColumnCount = 6
            .ColumnWidths = "80,100,100,80,100,80"
            .RowSource = sh.Name & ("!A2:W2")
            .AddItem
            .List(ListBEmpList.ListCount - 1, 0) = sh.Cells(i, 21)
            .List(ListBEmpList.ListCount - 1, 1) = sh.Cells(i, 2)
            .List(ListBEmpList.ListCount - 1, 2) = sh.Cells(i, 14)
            .List(ListBEmpList.ListCount - 1, 3) = sh.Cells(i, 13)
            .List(ListBEmpList.ListCount - 1, 4) = sh.Cells(i, 10)
            .List(ListBEmpList.ListCount - 1, 5) = sh.Cells(i, 22)
              
        End With
        
        Next i

    Saturday, September 19, 2020 6:59 PM

All replies

  • The most important point is that you can either specify a range as the RowSource and set ColumnHeads to True, OR use the AddItem, List and Column methods to populate the list box. You can NOT combine these two ways.

    Apart from that: since the list box has 6 columns, there is no point in using A2:W2 as RowSource.

    And since the sheet name contains a space, you must enclose it in single straight quotes, just like in a cell formula that refers to that sheet.

    You can shorten the code to

        Dim LR As Long
        Dim sh As Worksheet
    
        ' Define the sheet first, so that you can refer to it
        Set sh = ThisWorkbook.Sheets("Employee Data")
        LR = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
        If LR = 1 Then LR = 2
    
        With Me.ListBEmpList
            .ColumnHeads = True
            .ColumnCount = 6
            .ColumnWidths = "80,100,100,80,100,80"
            ' Enclose the sheet name in single quotes
            ' and specify the range in columns A to F
            .RowSource = "'" & sh.Name & "'!A2:F" & LR
        End With


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Saturday, September 19, 2020 8:25 PM
  • Hi ... 

    Thanks for the reply and the tips...... 

    i tried using the codes that you edited and i m still getting the runtime error.... 

    Does it have anything to do with the columns that i have selected to show in in the listbox ? my sheet "Employee Data" have a total of 23 columns (A-W), but then columns that is used to show on the list is only that 6 from the sheet... 

    Sunday, September 20, 2020 4:39 PM
  • That shouldn't matter.

    I have uploaded a sample workbook to Dropbox: https://www.dropbox.com/s/rbhxqc2tcll1edk/ListBoxDemo.xlsm?dl=1

    If you can't get it to work:

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Sunday, September 20, 2020 7:34 PM