Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of comboboxes
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comboboxes | Excel Discussion (Misc queries) | |||
Comboboxes | New Users to Excel | |||
Need help with ComboBoxes. | New Users to Excel | |||
Read comboboxes into array | Excel Programming | |||
Comboboxes | Excel Programming |