Initialize multiple combo boxes - Invalid qualifier error

[jjfeenix]

I have a doc with a series of combo box controls that all need to be
initialized with the same list. This only needs to be done once, when a doc
is started from the template, so I put the initialization code into the
Document_New private sub.

If I call the combo box by it's name, it's fine (ex: cbCategory01). But I
want to initialize all 24 in a loop. I thought I could do this with a loop
that fed the combo box name as a constructed sting variable (cbCategoryName =
"cbCategory" & "01") where "01" is incremented to "02" thru "24". But I get
invalid qualifier errors. I moved the initialization code to a Public sub,
called from the Document_New, but still get the error. (I know that the code
below doesn't contan a loop, because I can't get it to work for just the
first iteration. )

Public Sub InitializeCategory()

Dim cbCategoryName As String
cbCategoryName = "cbCategory" & "01"


cbCategoryName.Clear
cbCategoryName.BackColor = cellWhite
cbCategoryName.ForeColor = cellBlack

End Sub

Any help would be appreciated,

TIA

 

[Jay Freedman]

I have a doc with a series of combo box controls that all need to be
initialized with the same list. This only needs to be done once, when
a doc is started from the template, so I put the initialization code
into the Document_New private sub.

If I call the combo box by it's name, it's fine (ex: cbCategory01).
But I want to initialize all 24 in a loop. I thought I could do this
with a loop that fed the combo box name as a constructed sting
variable (cbCategoryName = "cbCategory" & "01") where "01" is
incremented to "02" thru "24". But I get invalid qualifier errors. I
moved the initialization code to a Public sub, called from the
Document_New, but still get the error. (I know that the code below
doesn't contan a loop, because I can't get it to work for just the
first iteration. )

Public Sub InitializeCategory()

Dim cbCategoryName As String
cbCategoryName = "cbCategory" & "01"


cbCategoryName.Clear
cbCategoryName.BackColor = cellWhite
cbCategoryName.ForeColor = cellBlack

End Sub

Any help would be appreciated,

TIA

Well, cbCategoryName is declared as a String variable, so it stands to
reason that it doesn't have a .Clear method or any Color properties. You
have to get hold of the object whose name matches the value of
cbCategoryName. In this somewhat tortured corner of the Word object model, a
combo box is represented as the .OLEFormat.Object property of an InlineShape
object. Not all InlineShape objects are combo boxes, though, so you need to
iterate that collection and test each one until you find the right one.

Here's some code I just posted to the thread "For Next Loop" in the
microsoft.public.word.docmanagement newsgroup to solve the same problem:

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

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

 

[jjfeenix]

Jay:

Thanks very much. Your solution worked.

But I'm still foggy on why I need to parse all the ILS objects to Set a
reference to Combo boxes that I already know that names of

There's no way to Set the Combo box variable to the
oILS.OLEFormat.Object.Name?(names = cbCategory1, cbCategory2, etc.
.....cbCategory24)

TIA

 

[Jay Freedman]

Jay:

Thanks very much. Your solution worked.

But I'm still foggy on why I need to parse all the ILS objects to Set a
reference to Combo boxes that I already know that names of

There's no way to Set the Combo box variable to the
oILS.OLEFormat.Object.Name?(names = cbCategory1, cbCategory2, etc.
....cbCategory24)

TIA

Sorry, no, there isn't.

You can do this either of two ways. One is the loop I showed you. The other is
to pass each ComboBox object itself to a subroutine:

Private Sub InitializeOneBox(oCombo As ComboBox)
oCombo.Clear
oCombo.BackColor = cellWhite
oCombo.ForeColor = cellBlack
End Sub

Public InitializeCategory()
InitializeOneBox cbCategory1
InitializeOneBox cbCategory2
InitializeOneBox cbCategory3
InitializeOneBox cbCategory4
InitializeOneBox cbCategory5
' etc.
End Sub

 

[jjfeenix]

Jay:

You've been gracious in answering my queries.

I'll give your second method a try. I'm probably going to have more than 100
ILS.OLEFormat.Objects in the final document,. So I'm thinking that searching
thru them all the time might be slower than just "feeding" them by name to
the function.

If I'm not imposing too much, I have an earlier query, related to this same
project, that has not gotten any response. Maybe you could give me your
opinions on this subject also?

"Word 2003: VBA controls used in tables" from 7/25/09.

Once more, thanks for the excellent help!
JohnS