Variable as Object

[squeebles]

i have some labels in a word document that i can manipulate from excel but i want to be able to change each label with one sub. the naming of my labels is in the form of "Label5y" and "Label5n". this sub is meant to determine the contents of a cell and change the labels accordingly. this sub works if i hard code the name of each label but i need them to change dynamically. if there is a different way to go about this i am all ears.

Sub yesNo(col As String, ynLabel As String, i)

Dim labely As String
Dim labeln As String

labely = "Label" & ynLabel & "y"
labeln = "Label" & ynLabel & "n"

If Range(col & i) = "yes" Then
appWD.ActiveDocument.labely.Caption = "(Yes)"
appWD.ActiveDocument.labeln.Caption = "No"
appWD.ActiveDocument.labeln.Font.Strikethrough = True
Else
appWD.ActiveDocument.labely.Caption = "Yes"
appWD.ActiveDocument.labely.Font.Strikethrough = True
appWD.ActiveDocument.labeln.Caption = "(No)"
End If

End Sub

 

[Norie]

How have you created these labels?

Are they ActiveX labels?

 

[hahdawg]

You want something like:

appWD.ActiveDocument.labels(labely).Caption

 

[squeebles]

How have you created these labels?

Are they ActiveX labels?

yes these are activex labels.

 

[squeebles]

You want something like:

appWD.ActiveDocument.labels(labely).Caption

i will try this.

result:

Object doesn't support this property or method.

 

[Norie]

I don't think Labels will work.

I have found a couple of ways to refer to ActiveX labels on a Word document but not by name.

I'm going to have a Google now to see if I can find something for that.

 

[hahdawg]

Sorry-didn't see that it was an ActiveX object.

I don't know Word VBA, but you'll need something like:

Dim MyObj As OLEObject
For Each MyObj In ActiveDocument.OLEObjects
    If MyObj.Name = labely Then
        MyObj.Object.Caption = "(Yes)"
    End If
Next MyObj

That might not work right off the bat (it'd work for sure if the label were in Excel), but it's the right idea. You might have to record a macro in Word to get the syntax correct; for example, ActiveDocument.OLEObjects might be ActiveDocument.SomethingElse.OLEObjects.

 

[Norie]

hahdawg

Unfortunately OLEObjects doesn't appear to exist in Word.

The only ways I found were InlineShapes and Fields, but as I said neither allows you to use the name.

I'm sure there's something I'm missing though which is why I'm still looking.

 

[squeebles]

i recorded a macro in word but it would not let me edit the label. however the code for adding the label is as such:

Selection.InlineShapes.AddOLEControl ClassType:="Forms.Label.1"

btw thank you both for your responses

 

[squeebles]

bah why can i not delete posts?