How to Reference Combo Box by Name in Word VBA

[Michael_Blue]

I want to use code like the below (modified from
http://support.microsoft.com/kb/253542), but I don't want to hard code the
name of the Combo box. How to reference it by name?
ActiveDocument.FormFields(cmbName) is not working for me...

Function PopulateTestComboBox(cmbName As String)
Dim dbDatabase As Database
Dim rst As Recordset
Dim i As Integer

Set dbDatabase = OpenDatabase(strDBPath)
Set rst = dbDatabase.OpenRecordset("tbl_Tests", dbOpenSnapshot)

i = 0
Do Until rst.EOF
ActiveDocument.FormFields(cmbName).AddItem (i)
ActiveDocument.FormFields(cmbName).Column(0, i) =
rst.Fields("TestName")
'ComboBox1.AddItem (i)
'ComboBox1.Column(0, i) = rst.Fields("TestName")
rst.MoveNext
i = i + 1
Loop
End Function

 

[Jay Freedman]

First, I'll assume that you're working in a userform like the one described
in the KB article, rather than using an ActiveX combo box directly in the
document body. If I'm wrong, the rest of this should be disregarded.

In the userform code, ComboBox1 refers to a combo box control *on the
userform*, so it isn't a member of the ActiveDocument.FormFields
collection -- it isn't in the ActiveDocument, and even if it were, it isn't
a form field. It's a member of the userform's Controls collection.

Next, I'll assume that you have some other code in the userform that's going
to call this PopulateTestComboBox procedure (which should be a Sub and not a
Function, because it doesn't return any value). That other code is going to
know which combo box to refer to, because it has to know what value to pass
to cmbName.

You might think you could try something like

Me.Controls(cmbName).Column(0, i) = rst.Fields("TestName")

but that won't work because addressing the combo box as a member of the
Controls collection returns a Control object -- sort of a generic thing that
could be a combo box or a text box or a spinner, etc. -- and the Control
object doesn't have a Column property.

The solution is to have that other code pass the actual combo box object as
the argument rather than just its name. First change the argument of the
PopulateTestComboBox procedure to accept a ComboBox object, and then call it
with the desired combo box object (not just its name) as the argument.

Here's a very simplified illustration. Make a userform containing two combo
boxes and two buttons, all with the default names. Put this code in the
userform, and see what happens when you click first one button and then the
other:

Private Sub CommandButton1_Click()
PopulateTestComboBox Me.ComboBox1
End Sub

Private Sub CommandButton2_Click()
PopulateTestComboBox Me.ComboBox2
End Sub

Private Sub PopulateTestComboBox(cmb As ComboBox)
Dim i As Integer
For i = 1 To 3
cmb.AddItem Format(i, "0000")
Next
cmb.ListIndex = 0
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Michael_Blue]

Unfortunately I am using an ActiveX combo box but thanks for all the info!