NickHK
12/13/2006 2:08:00 AM
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" <dbenjamin@seagen.com> wrote in message
news:OVWY94jHHHA.5000@TK2MSFTNGP03.phx.gbl...
> 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
>
>