Array of comboboxes

D

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
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top