Populating a Multi column Combobox in a userform

[anandvh]

Hi,

I am working on a project relating to employee management. I have created a UserForm which enables the user to select the employee for modification, reports, etc.

This userform has a combobox which I want to populate with an two dimensional array that has 4 columns and as many rows as there are employees. The employee data is stored in a worksheet called "PERS". I want the combobox to display the 4 columns as Employee ID (Column C), Surname (Column F), First name (Column G) and Middle Name (Column H). The data starts in row 3. Headers occupy rows 1 and 2.

I am having a problem with loading the combobox with the data. The code that I have currently is given below

Private Sub UserForm_activate()
     
    Dim MyArray As Variant
    Dim tRow As Long
    tRow = Module1.xlLastRow("PERS") - 2
    ReDim MyArray(1 To tRow, 1 To 4) ' declare MyArray as a dynamic array

    
    ' define the properties of the combobox
    With ComboBox1
        .ColumnCount = 4
        .ColumnWidths = 75
        .Width = 350
        .Height = 15
        .ListRows = 6
    End With
     
    ' Define the list and where it's obtained from.
    ' This array will have 4 columns and number of rows will be defined by value of tRow
    ' First column is the membership number, Col. C
    ' Second column is the Surname, Col F
    ' Third column is the First Name, Col G
    ' Fourth column is the Middle Name, Col H
    With ThisWorkbook.Worksheets("PERS")
    
        ' Populate the first column with the membership number
        Dim iCol As Integer, iRow As Integer, iSou As Integer
        iCol = 0            ' column number. First column no value is always zero
        iRow = 0            ' row number. First row no is always zero
        iSou = 3            ' row number in worksheet to start the population
    
        Do Until iSou = iLst                                    ' start the loop to populate the membership number
            MyArray(iRow, 0) = .Range("C" & iSou).Value
            ' increment the counters
            iRow = iRow + 1     ' next row in the array
            iSou = iSou + 1     ' next row in the source worksheet
        Loop
    
         
    End With
    ' populate the combo box
    ComboBox1.List() = MyArray

End Sub

I keep getting error "Subscript out of range" on the line

 MyArray(iRow, 0) = .Range("C" & iSou).Value

Can some one help point out where I have gone wrong and give the solution to populate the combobox. I have been searching the web for likely solutions but none have worked so far.

Please excuse if there is an elementary error - I am still low on the learning curve

Thanks in advance for your patience

Anand