Array to Userform Listbox

[Airn5475]

Hello,
I have been searching the internet and this sight and have found many variations, but I cannot seem to get my listbox on a Userform to populate.
The array is 10 columns by 5 rows. I can get it to add one long column of data, but this is obviously now what I want.

Can anyone help or point me in the right direction?
Thank you in advance,
Aaron

 

[NateO]

Okay, nevermind simplicity...

If you want 'proper' headers that's the only way your going to get them.

You can have headers and use an Array. With a little creativity, VBA is extremely flexible and easily allows for this. Let's get back to what I was saying:

If you want to pass certain vectors of your Array, I think you need to create an Array you'd actually like you use.

Right, no joke. Here's what I did, I took Range("A1:E10"), and created a new Array with Headers and only the values in Columns A, C & E:

Sub foo()
Dim varArr() As Variant, strArr() As String
Dim headArr() As Variant
Dim i As Long, j As Long

Let headArr = Array("Foo", "Bar", "Baz")
Let varArr = Range("A1:E10").Value

ReDim strArr(1 To UBound(varArr) + 1, 1 To 3)

For i = LBound(headArr) To UBound(headArr)
    Let strArr(1, i + 1) = headArr(i)
Next

For i = LBound(varArr, 1) To UBound(varArr, 1)
    For j = 1 To 3
        Let strArr(i + 1, j) = varArr(i, j * 2 - 1)
    Next
Next

Load UserForm1
With UserForm1.ListBox1
    .Clear
    .ColumnCount = 3
    .List = strArr
    .ListIndex = 0
End With
UserForm1.Show

End Sub

I take it back, don't Transpose your Array, that was specific to the one I hardcoded, i.e., this shows up exactly as it appears in my Spreadsheet. Yes-no? :wink:

 

[Airn5475]

Ok I got that example to work properly except that first row of data in my spreadsheet that is my column headers are being listed as rows and are selectable. How would I get around that? (Sorry to be picky)

 

[Norie]

Nate

Aren't those headers just items in the listbox?

 

[NateO]

Ok I got that example to work properly except that first row of data in my spreadsheet that is my column headers are being listed as rows and are selectable. How would I get around that? (Sorry to be picky)

It sounds to me like you need to create a whole new Spreadsheet:

http://support.microsoft.com/kb/164343

I know of no workaround, sorry.

 

[NateO]

Nate

Aren't those headers just items in the listbox?

Yes.

 

[Norie]

Nate

So they aren't actually headers then.

And are selectable, which I don't think the OP wants.

my column headers are being listed as rows and are selectable

Note I'm not saying there's anything wrong with the method.

And the fact that the headers can be selected can easily be dealt with, just needs a little more code.

 

[Airn5475]

If I follow you, you're saying that there isn't any way to turn those into the actual column headings which are not selectable, correct?

Also,
I am getting the following error when I exit from the user form

Object variable or With block variable not set (Error 91)

 

[NateO]

So they aren't actually headers then.

Technically per Microsoft's literal ColumnHeads Property, that doesn't work very well? I suppose not, but in reality? Yes, they are headers.

Hey, seriously, are you here to argue with me, or help Aaron with this quandary? I don't necessarily need the debate, and I'm not sure Aaron does either... How about a solution?

This is a distraction, should I help Aaron, argue with you, or all of the above?

If I follow you, you're saying that there isn't any way to turn those into the actual column headings which are not selectable, correct?

Not what Microsoft or I am saying. Populate a new Worksheet with your data, supplying a row of header data, and provide the Range as the 'RowSource' property, one row below your headers, and specifiy that you do in fact have a header row, e.g.,

Sub foo()
Load UserForm1
With UserForm1.ListBox1
    .RowSource = "A2:E10"
    .ColumnHeads = True
    .ColumnCount = 5
End With
UserForm1.Show
End Sub

And note, this does not have to be done through code, you can hardcode all of this through the ListBox's properties.

 

[Norie]

Aaron

This seemed to work for me.

Private Sub UserForm_Initialize()
Dim wsData As Worksheet
Dim rngData As Range
Dim LastRow As Long


    LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsData = Worksheets.Add
    
    With Worksheets("Sheet1")
        Union(.Range("A1").Resize(LastRow), .Range("C1").Resize(LastRow), .Range("E1").Resize(LastRow)).Copy wsData.Range("A1")
    End With
    
    With ListBox1
        .ColumnHeads = True
        .ColumnCount = 3
        .RowSource = wsData.Range("A1").CurrentRegion.Offset(1).Resize(wsData.Range("A1").CurrentRegion.Rows.Count - 1).Address
    End With
    
    Application.DisplayAlerts = False
    wsData.Delete
    Application.DisplayAlerts = True
    
End Sub

It populated a listbox from non-contigous columns, including the headers.

The only problem I can see is that I needed to use a new (temporary) worksheet and that might affect how things are done later.

 

[Airn5475]

Not what Microsoft or I am saying. Populate a new Worksheet with your data, supplying a row of header data, and provide the Range as the 'RowSource' property, one row below your headers, and specify that you do in fact have a header row, e.g.,

Ok, not to try and continue to make this complicated, but how would I do this with the array that has only certain columns? I keep getting errors complaining when I try to do:

.RowSource = strArr