Array of comboboxes

[Dennis Benjamin]

Hi All

I'm working with a spreadsheet where I want to present the user with several
comboboxes, the contents of which are pulled from an Access database. As the
project grew, I kept adding Comboboxes, and today decided that instead of
working one by one with the tablenames and the Comboboxes I would make an
array of each and then use one loop to step throgh them. The one-by-one code
that worked looked like:

'Instantiate the Connectionobject.
Set cnt = New ADODB.Connection

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

'Create the SQL-statement.
stTableName = "[Animal Species]"
stSQL1 = "SELECT * FROM " & stTableName
With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnected
recordset.
.Open stConn 'Open connection.
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL1)
With rst
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
'Populate the array with the whole recordset.
vaSpeciesData = .GetRows
End With
End With

'Close the connection.
cnt.Close

'Manipulate the Combobox's properties and show the form.
With Worksheets(1).Species_Combo
.Clear
.ColumnCount = 2
.ColumnWidths = "0;20"
.BoundColumn = 1
.TextColumn = k
.List = Application.Transpose(vaSpeciesData)
' .ListIndex = -1
End With

So I made an array of strings for the Table names, and an array of
comboboxes like so:

Dim sTables(1 To 3) As String
Dim cmbBoxes(1 To 3) As ComboBox

sTables(1) = "[Animal Species]"
sTables(2) = "[tbl Projects]"
sTables(3) = "tblCellLines"

Set cmbBoxes(1) = Worksheets(1).Species_Combo.Object
Set cmbBoxes(2) = Worksheets(1).Project_Combo.Object
Set cmbBoxes(3) = Worksheets(1).Model_Combo.Object

and everything goes up until I try

With cmbBoxes(i)
.Clear
.ColumnCount = 2
.ColumnWidths = "0;20"
.BoundColumn = 1
.TextColumn = k
.List = Application.Transpose(vaData)
' .ListIndex = -1
End With


The .Clear statement crashes Excel. So, can I do what I'm trying to, and if
so, where did I go wrong?

Thanks for any help!

Dennis

 

[NickHK]

Dennis,
This works for me, with Combos from the Controls tool box. Think you are
missing the "MSForms" :

Private Sub CommandButton1_Click()
Dim cbo(1 To 3) As MSForms.ComboBox
Dim i As Long

Set cbo(1) = Worksheets(1).ComboBox1
Set cbo(2) = Worksheets(1).ComboBox2
Set cbo(3) = Worksheets(1).ComboBox3

For i = 1 To 3
With cbo(i)
.Clear
Debug.Print .ListCount
End With
Next

End Sub

NickHK