ExcelBanter - Populate a ComboBox

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Populate a ComboBox (https://www.excelbanter.com/excel-programming/385892-populate-combobox.html)

Populate a ComboBox

I used the code shown below to populate a combobox in a Word UserForm.

It basically cycles through each record and loads the value of field
1, 2 or 3 into the combobox depending on a user selection.

Is there a way to use the combobox '.List" method to populate the list
all at once vice cycling through each record. Something like:

me.combobox.List = mySSRange Column 1

Thanks.

Private Sub UserForm_Initialize()
'You need to set a reference in your project to the
'"Microsoft DAO 3.51 (or 3.6) Object Library".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
'Open a spreadsheet to retrieve data
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
i = 0
'Loop through each recordset.
Me.ComboBox1.Clear
While Not rs.EOF
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
Me.ComboBox1.AddItem rs.Fields(0).Value
Case "B"
Me.ComboBox1.AddItem rs.Fields(1).Value
Case "C"
Me.ComboBox1.AddItem rs.Fields(1).Value
End Select
rs.MoveNext
i = i + 1
Wend
'Clean up.
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Populate a ComboBox

Populate a ComboBox

Jim,

Thanks for the reply. I can't make that work. I adapted my code as
follows and when I executre I get a RTE "Sub or function not defined
on the first use of Range:

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Open a spreadsheet to retrieve data
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
Me.ComboBox1.Clear
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
Me.ComboBox1.List = Range("A1:A3").Value
Case "B"
Me.ComboBox1.List = Range("B1:B3").Value
Case "C"
Me.ComboBox1.List = Range("C1:C3").Value
End Select
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

On Mar 22, 1:26 pm, "Jim Rech" wrote:
Me.ComboBox1.List = Range("A1:A3").Value

--
Jim"Greg Maxey" wrote in message

ps.com...
|I used the code shown below to populate a combobox in a Word UserForm.
|
| It basically cycles through each record and loads the value of field
| 1, 2 or 3 into the combobox depending on a user selection.
|
| Is there a way to use the combobox '.List" method to populate the list
| all at once vice cycling through each record. Something like:
|
| me.combobox.List = mySSRange Column 1
|
| Thanks.
|
| Private Sub UserForm_Initialize()
| 'You need to set a reference in your project to the
| '"Microsoft DAO 3.51 (or 3.6) Object Library".
| Dim db As DAO.Database
| Dim rs As DAO.Recordset
| Dim i As Long
| 'Open a spreadsheet to retrieve data
| Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
| Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
| i = 0
| 'Loop through each recordset.
| Me.ComboBox1.Clear
| While Not rs.EOF
| Select Case ActiveDocument.FormFields("Dropdown1").Result
| Case "A"
| Me.ComboBox1.AddItem rs.Fields(0).Value
| Case "B"
| Me.ComboBox1.AddItem rs.Fields(1).Value
| Case "C"
| Me.ComboBox1.AddItem rs.Fields(1).Value
| End Select
| rs.MoveNext
| i = i + 1
| Wend
| 'Clean up.
| rs.Close
| db.Close
| Set rs = Nothing
| Set db = Nothing
| End Sub
|

Populate a ComboBox

Jim,

I shifted from the DOA approach to this which seems to work using your
suggestion:

Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Set xlApp = CreateObject("Excel.Application")
'Open the spreadsheet to get data
Set xlWB = xlApp.Workbooks.Open("C:\Book1.xls")
Set xlWS = xlWB.Worksheets(1)
Me.ComboBox1.Clear
Select Case ActiveDocument.FormFields("Dropdown1").Result
Case "A"
Me.ComboBox1.List = xlWS.Range("A1:A3").Value
Case "B"
Me.ComboBox1.List = xlWS.Range("B1:B3").Value
Case "C"
Me.ComboBox1.List = xlWS.Range("C1:C3").Value
End Select
'Clean up.
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

On Mar 22, 1:26 pm, "Jim Rech" wrote:
Me.ComboBox1.List = Range("A1:A3").Value

--
Jim"Greg Maxey" wrote in message

ps.com...
|I used the code shown below to populate a combobox in a Word UserForm.
|
| It basically cycles through each record and loads the value of field
| 1, 2 or 3 into the combobox depending on a user selection.
|
| Is there a way to use the combobox '.List" method to populate the list
| all at once vice cycling through each record. Something like:
|
| me.combobox.List = mySSRange Column 1
|
| Thanks.
|
| Private Sub UserForm_Initialize()
| 'You need to set a reference in your project to the
| '"Microsoft DAO 3.51 (or 3.6) Object Library".
| Dim db As DAO.Database
| Dim rs As DAO.Recordset
| Dim i As Long
| 'Open a spreadsheet to retrieve data
| Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
| Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
| i = 0
| 'Loop through each recordset.
| Me.ComboBox1.Clear
| While Not rs.EOF
| Select Case ActiveDocument.FormFields("Dropdown1").Result
| Case "A"
| Me.ComboBox1.AddItem rs.Fields(0).Value
| Case "B"
| Me.ComboBox1.AddItem rs.Fields(1).Value
| Case "C"
| Me.ComboBox1.AddItem rs.Fields(1).Value
| End Select
| rs.MoveNext
| i = i + 1
| Wend
| 'Clean up.
| rs.Close
| db.Close
| Set rs = Nothing
| Set db = Nothing
| End Sub
|