Multiple ComboBoxes from Excel Named Range

[DGjr.]

How do I take the following code and build a repeating loop to build an
identical list in 13 different ComboBoxes (i.e. ComboBox1 thru ComboBox13)?

Private Sub Document_Open()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\Filename.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `SftwrProd`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ComboBox1.ColumnCount = rs.Fields.Count


' Load the ComboBox with the retrieved records
ComboBox1.Column = rs.GetRows(NoOfRecords)


' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing
End Sub

Thanks.... DGjr.

 

[Chuck]

Assuming your comboboxes are in a userform (such as "UserForm1") try
replacing the relevant lines in your code with the following (there may be
more elegant ways of doing this however):

Dim i As Long
Dim cntControl As Control

For Each cntControl In UserForm1.Controls
For i = 1 To 13
If cntControl.Name = "ComboBox" & i Then
' Set the number of Columns = number of Fields in recordset
cntControl.ColumnCount = rs.Fields.Count
' Load the ComboBox with the retrieved records
cntControl = rs.GetRows(NoOfRecords)
End With
End If
Next i
Next cntControl

 

[DGjr.]

Chuck -

I've tried getting this to work with my existing code and can't. I'm new to
VBA. Can I just set up some sort of loop with my existing code (supplied in
original thread)?

 

[Chuck]

I tested the code I posted as described (replacing relevant lines in your
code with my code) and it worked.

Unless you have a userform to which the code refers (ie ComboBox1 being a
combobox on a user form) then the code will not work because it's referring
to non-existent controls.