Problem populating listbox from range (Subscript out of range error)

[jmbhoy]

Hi,

On the change of a combobox that lists all worksheets within the current workbook, I am trying to dynamically populate a list box on the same form, from data contained in the 1st row of the chosen worksheet.

I am receiving a "subsript out of range" error on the .AddItem MyList(i) line. My code is below, any help in resolving this would be really appreciated.

Many Thanks

Example Worksheet = "Sheet 1"
A B C D E F
1 Col1 Col2 Col3 Col4 Col5 Col6

List Box Data should be

Col1
Col2
Col3
Col4
Col5
Col6

------------------------------------------------------------------

Private Sub ComboBox1_Change()
Dim WorksheetName As String
Dim rngSel As String
Dim lastcol As Long
Dim MyList As Variant, i As Long

WorksheetName = ComboBox1.Value

'Check for optional worksheetname else use activesheet
If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If

With Worksheets(WorksheetName)

With Me.lbxColumns

.Clear
.ColumnCount = 1
.TextColumn = True

' Calls function to retrieve last populated column on spreadhseet
lastcol = xlLastCol(WorksheetName)

' Retrieves Excel data range and converts to address string
rngSel = (Range((Cells(1, 1)), (Cells(1, lastcol))).Address)

MyList = Worksheets(WorksheetName).Range(rngSel).Value ' get the values you want
MyList = Application.WorksheetFunction.Transpose(MyList) ' convert values to a vertical array
For i = 1 To UBound(MyList)
.AddItem MyList(i) ' <<<< SUBSCRIPT OUT OF RANGE ERROR
Next i

.ListIndex = 0

End With

End With

End Sub

 

[RoryA]

It's actually a 2D array, so you need to specify the second dimension:

.AddItem MyList(i, 1)

 

[Andrew Poulsom]

Or, you can assign the array to the ListBox's List property:

.List = MyList

 

[jmbhoy]

Many thanks. I specificed the 2nd dimension and it worked fine