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

 

[jindon]

Airn5475

1) .RowSource property only accepts string type data to set and only from the range, not via vba Array.
So it needs to be set something like

"A1:A100", "Sheet1!A1:A100"

2) What do you mean "array that has certain columns?"

 

[Airn5475]

I have a spreadsheet with different columns that will be updated and referenced in the duration of my my macro, but out of the 6 columns, I only want to display 3 of them, let's say A, B & E.
I am able to create an Array that only holds these three columns, but I am still looking for a way to get this information into a list box with real column headers that are not selectable.

Does that make sense?

 

[Norie]

Aaron

Did you try the code I posted?

 

[Airn5475]

Did you try the code I posted?

Sorry Norie I did not, like I said before I was really hoping to get the data from my array instead of straight from my spreadsheet. Nate0's code worked and his was from an array and it's probably what I will use, I was just hoping to get real column headers.
Thanks for the suggestion though!

 

[Norie]

Aaron

Why are you insisting on using an array?

There is no way that I know of that you'll get real column headers using that method.

Nate's workaround is neat, so if it satisfies your needs use it.

There could be ways you could prevent selection of the header row.

Or at least simulate that.

 

[Airn5475]

There is no way that I know of that you'll get real column headers using that method.

My apologies Norie, this was the answer I was looking for. I was happy with what Nate0 gave me and will be using it, but I was just looking for a "Yes, it's possible" or "No, it's not possible" answer, but instead I kept getting more suggestions of how to do what I didn't want to do (This is why I kept asking.)
I prefer the array, because it will keep my code consistent.

To conclude my findings based on the above replies:
1) Arrays can be used to populate listboxes, but
2) You cannot use the top row of the array as an "un-selectable" column header.

Once again I apologize to everyone and I thank you all for you help.

 

[Airn5475]

I am still getting the following error when I exit from the following code:

Run-time error '91':
Object variable or With block variable not set

Private Sub UserForm_Initialize()
Dim varArr As Variant
Dim strArr As Variant

ProcColCnt = ThisWorkbook.Worksheets("PROCS").UsedRange.Columns.Count
ProcRowCnt = ThisWorkbook.Worksheets("PROCS").UsedRange.Rows.Count - 1
Debug.Print "Proc Columns: " & ProcColCnt & "; Proc Rows: " & ProcRowCnt
Set ProcRng = ThisWorkbook.Worksheets("PROCS").UsedRange
Set ProcArr = ProcRng.Resize(ProcColCnt, ProcRowCnt).Offset(1, 0)
Let varArr = ProcRng.Resize(ProcColCnt, ProcRowCnt).Offset(1, 0)

For Row = 1 To ProcRowCnt
    ProcArr(Row, 5) = "NULL"
    ProcArr(Row, 6) = "NULL"
    ProcArr(Row, 7) = "NULL"
    ProcArr(Row, 8) = "NULL"
Next Row

ReDim strArr(1 To ProcRowCnt, 1 To 3)

For Row = 1 To ProcRowCnt
    For Column = 1 To 3
        Debug.Print "Column: " & Column
        Select Case Column
            Case 1
                Let strArr(Row, Column) = varArr(Row, 1)
                Debug.Print varArr(Row, 1)
            Case 2
                Let strArr(Row, Column) = varArr(Row, 2)
                Debug.Print varArr(Row, 2)
            Case 3
                Let strArr(Row, Column) = varArr(Row, 4)
                Debug.Print varArr(Row, 4)
        End Select
    Next
Next
Load MainForm
With MainForm.lboxProcs
    .Clear
    .ColumnCount = 3
    .List = strArr
    .ListIndex = 0
End With
MainForm.Show
End Sub

Private Sub btnExit_Click()
    Application.ScreenUpdating = True
    Unload MainForm
End Sub

Can anyone assist?