Multicolumn Combo Box

[PokerDude]

I am trying to create code that will populate a combo box from an external
file. I am stumped why I am receiving an out of script error and basically
why my code isnt working. Could someone please tell me what I am doing
wrong?

Private Sub CommandButton1_Click()
Dim rowInx, numRows As Long
Dim MyArray() As String


'Set ComboxBox properties **
ComboBox1.BoundColumn = 1
ComboBox1.ColumnCount = 3
ComboBox1.ColumnWidths = "50 pt" & ";" & "200pt" & ";" & "50pt"
ComboBox1.ColumnHeads = True
ComboBox1.ListWidth = 325
ComboBox1.LinkedCell = "A6"

'this worksheet is 20 columns wide and at least 25 rows and is updated daily
**
'only the first 3 columns is needed to be displayed in the combobox **
Workbooks.Open Filename:="c:\CCF\Contracts1.xls"
With ThisWorkbook.Worksheets("Sheet1")

'Determine number of rows **
numRows = Application.CountA(ActiveSheet.Range("A:A"))

'Clear Listindexes **
ComboBox1.Clear

'Refresh listindexes with current data **
'the combobox should display 3 columns to the user **
'the combobox is embedded on a worksheet, not a userform **

For rowInx = 1 To numRows

MyArray(rowInx, 0) = ActiveSheet.Cells(1, (rowInx + 1)).Value
MyArray(rowInx, 1) = ActiveSheet.Cells(1, (rowInx + 1)).Value
MyArray(rowInx, 2) = ActiveSheet.Cells(1, (rowInx + 1)).Value


Next rowInx

'Load data into combobox ***
ComboBox1.Column() = MyArray
ComboBox1.ListIndex = 0
End With
ActiveWorkbook.Close

End Sub

 

[Patrick Molloy]

the last few lines of your code should be changed to.....

'Refresh listindexes with current data **
'the combobox should display 3 columns to the user **
'the combobox is embedded on a worksheet, not a userform
**
ReDim MyArray(1 To numRows, 0 To 2)
For rowInx = 1 To numRows

MyArray(rowInx, 0) = ActiveSheet.Cells(rowInx, 1).Value
MyArray(rowInx, 1) = ActiveSheet.Cells(rowInx, 2).Value
MyArray(rowInx, 2) = ActiveSheet.Cells(rowInx, 3).Value

Next rowInx

'Load data into combobox ***
ComboBox1.List = MyArray
ComboBox1.ListIndex = 0

'ActiveWorkbook.Close

End Sub

Patrick Molloy
Microsoft Excel MVP

 

[PokerDude]

This works beautifully in my test environment.

When I corrected the code in my actual application I
receive an run-time error 9, subscript out of range when
it hits the following 2nd line of code:

Workbooks.Open Filename:="Z:\HOME\SHARED\Contracts.xls"
With ThisWorkbook.Worksheets("Sheet1")

I validated that the path, file and worksheets name was
correct. I am once again stumped.

In Project Explorer displays the following for
Contract.xls:
Sheet4 (Sheet1)

Its obvious Sheet1 is what the user names the sheet. What
is the technical term for Sheet4? I tried using both
Sheet1 & Sheet4 in that line of code. Both times it
results in the same run-time error.

Any ideas?

Thanks again
Mike