Owen said:
I have a Word 2003 document with 10 ComboBoxes named ComboBox1 to
ComboBox10.
My Macro below works correctly, although i would like to be able to
test all 10 ComboBoxes without having to repeat the code. Can i use a
For...Next loop where it tests for all 10 ComboBoxes?
If ActiveDocument.ComboBox1.Text = "black" Then
...
...
etc
I tried the following without success:
For j = 1 to 10
oCombo = ComboBox & j
If ActiveDocument.oCombo.Text = "black" Then
...
...
Next j
This will never work:
because the syntax is messed up in several ways. First, the word ComboBox
needs quotes around it to make it a string; otherwise VBA considers it to be
a variable name as assigns it the value 0 (unless you have the statement
Option Explicit at the top of your module; see
http://www.word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm).
Second, after you correct that, you have a string on the right side of the
equal sign, but you can't assign a string to an object (it's a type
mismatch, unless oCombo is also undeclared, in which case you get a Variant
variable with the string in it but that's not a combo box).
Worse, Word VBA can't use a string as the index to the combo boxes (or other
ActiveX controls) in the body of a document, the way you could with a
bookmark name. Either you use the exact name of the combo box, like
ActiveDocument.ComboBox1, or you search through the InlineShapes collection
to find the one whose name matches what you're looking for.
Here's how to make it work, including the required declarations:
Private Function FindCombo(strName As String) As ComboBox
Dim oCombo As ComboBox
Dim oILS As InlineShape
' return is Nothing if strName isn't found
Set oCombo = Nothing
' look through the InlineShapes collection for
' one that is an OLEControl with the proper name
For Each oILS In ActiveDocument.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If LCase(oILS.OLEFormat.Object.Name) = LCase(strName) Then
Set oCombo = oILS.OLEFormat.Object
Exit For
End If
End If
Next
Set FindCombo = oCombo
End Function
Sub x()
Dim myCombo As ComboBox
Dim j As Integer
For j = 1 To 10
Set myCombo = FindCombo("ComboBox" & CStr(j))
If Not myCombo Is Nothing Then
MsgBox myCombo.Value ' replace this with your test
End If
Next
End Sub
If you're working with ActiveX controls in a document, you should read
http://msdn2.microsoft.com/en-us/library/aa140269(office.10).aspx.
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
