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

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

"Dennis Benjamin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>