0

Problem: Objective is to export the data from ListBox1 and then separately export the headers. Data export OK, no problem. Header throws a runtime error 13, type mismatch. Do you guys know why? How can I fix this?

Private Sub ButtonSearchExport_Click()
    Dim wb As Workbook
    Set wb = ActiveWorkbook   '<<<< After export data, return to this original wb.
    With Workbooks.Add
        .Sheets(1).Range(Cells(2, 1), Cells(Me.ListBox1.ListCount, Me.ListBox1.ColumnCount)) = Me.ListBox1.List

 ''''''''<<<< The error is from the line here:
        .Sheets(1).Range(Cells(1, 1), Cells(Me.ListBox1.List(0), Me.ListBox1.ColumnCount)) = Me.ListBox1.List(0)

        Sheets(1).Name = "Transfer"
    End With
    wb.Activate
End Sub

1 Answer 1

1

You're using Me.ListBox1.List(0) as the row parameter in the second reference to Cells, but List(0) is (probably?) not a number. So this is an improvement:

.Sheets(1).Range(Cells(1, 1), Cells(1, Me.ListBox1.ColumnCount)) = Me.ListBox1.List(0)

But now you've got a range of cells (1D array), and List(0) will just return the single item in the first row and first column of the list. So that won't work either.

Unfortunately there is no way of extracting a single row or column from the List property as a 1D array. You can either get a single cell using .List(row, column) or you can get the whole array using .List. It gets worse: there's no way of directly setting or getting the column headers of a ListBox using VBA at all. They always come from the row of cells just above the range specified in the RowSource property of the ListBox.

Fortunately for you, that gives you an easy (if rather roundabout) way to access the column headers - just look at the worksheet cells that are the source for the ListBox data:

Range(ListBox1.RowSource).Offset(-1, 0).Copy .Sheets(1).Range(Cells(1, 1), Cells(1, Me.ListBox1.ColumnCount))

Note that this won't work if RowSource starts at the top of a sheet - if that's a possibility you'll need to test for it (I believe in that case the ListBox actually displays the Excel column headings - "Column A", "Column B" etc - as listbox headers!)

1
  • Aucuparia - thank you very much!! Very enlightening comments. I implemented, it works!
    – SofiaEd
    Jul 18, 2019 at 6:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .