Help With Listbox Array Code Please

[charllie]

Hi Folks,

I am absolutley hopeless with listboxes and arrays. However i have spent the last 2 hours having a go trying to combat it, lol.

Using VBA online help and MrExcel i have managed to creat the code below which places items from a worksheet into a userform listbox.

The list box contain 3 columns and list items from the worksheet Colums A2 to A7, C2 to C7 and H2 to H7.

I have tried to take it forward a step but cannnot find any thing that i understand well enough to do it either here on the net.

Instead at stopping at A7 i want it to include every row that has something in it, the same for C and H.

I would be grateful if someone could explain or show me how to do it. I just cannot grasp arrays.

Heres my code so far:

Dim SCH2 As Worksheet
    Dim i As Single
        Set SCH2 = Sheets("Schedule 2")
        'The 1st list box contains 3 data columns
        ListBox1.ColumnCount = 4
    
        'Load integer values into first column of MyArray
        For i = 0 To 5
            MyArray(i, 0) = i
        Next i
        'Load columns 2 and three of MyArray
        MyArray(0, 1) = SCH2.Range("A2")
        MyArray(1, 1) = SCH2.Range("A3")
        MyArray(2, 1) = SCH2.Range("A4")
        MyArray(3, 1) = SCH2.Range("A5")
        MyArray(4, 1) = SCH2.Range("A6")
        MyArray(5, 1) = SCH2.Range("A7")
        
        MyArray(0, 2) = SCH2.Range("C2")
        MyArray(1, 2) = SCH2.Range("C3")
        MyArray(2, 2) = SCH2.Range("C4")
        MyArray(3, 2) = SCH2.Range("C5")
        MyArray(4, 2) = SCH2.Range("C6")
        MyArray(5, 2) = SCH2.Range("C7")
        
        MyArray(0, 3) = SCH2.Range("H2")
        MyArray(1, 3) = SCH2.Range("H3")
        MyArray(2, 3) = SCH2.Range("H4")
        MyArray(3, 3) = SCH2.Range("H5")
        MyArray(4, 3) = SCH2.Range("H6")
        MyArray(5, 3) = SCH2.Range("H7")
    
        'Load data into ListBox1
        ListBox1.List() = MyArray

Thanks

 

[Norie]

Something like this?

Dim SCH2 As Worksheet
Dim I As Long
Dim LastRow As Long
Dim MyArray

    Set SCH2 = Sheets("Schedule 2")
    
    LastRow = SCH2.Range("A65536").End(xlUp).Row
    
    
    ReDim MyArray(LastRow - 2, 3)

    'The 1st list box contains 3 data columns
    ListBox1.ColumnCount = 4
    
    For I = 0 To LastRow - 2
        MyArray(I, 0) = I
        
        MyArray(I, 1) = SCH2.Range("A" & I + 2)
    
        MyArray(I, 2) = SCH2.Range("C" & I + 2)
    
        MyArray(I, 3) = SCH2.Range("H2" & I + 2)
    Next I
    
    ListBox1.List() = MyArray

 

[al_b_cnu]

Hi Mike,

You mean something like this NOTE - MyArray MUST be base 1 NOT Base 0)

Option Explicit
Dim MyArray()
Private Sub UserForm_Initialize()
Dim SCH2 As Worksheet
Dim i As Single
Dim iPtr1 As Integer, iPtr2 As Integer

ReDim MyArray(1 To 6, 1 To 4)
Set SCH2 = Sheets("Schedule 2")

'The 1st list box contains 3 data columns
ListBox1.ColumnCount = 4

For iPtr1 = 1 To 6
    MyArray(iPtr1, 1) = iPtr1
    For iPtr2 = 2 To 4
        MyArray(iPtr1, iPtr2) = SCH2.Range(Mid$(" ACH", iPtr2, 1) & iPtr1 + 1).Value
    Next iPtr2
Next iPtr1

'Load data into ListBox1
ListBox1.List = MyArray
End Sub

 

[charllie]

Hi Norie, Hi Alan,

Thank you both very much for the help, that works as required and i think i am also a step closer to understanding listboxes and arrays, lol.

I appreciate your help.

I have set the listStyle to StyleOption. I now want to be able to choose a line from the listbox and then send that information to:

• Column 1 to Me.Textbox8
  Column 2 to Me.Textbox14
  Column 3 to Me.Textbox9

Either by double clicking it (the preferred method) or by using a commandbutton.

Again i would apprecait your help.

Mike