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

 

[hahdawg]

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.

Well, that stinks. Maybe create a custom class of InlineShapes in Word and add the Name property to it?

 

[squeebles]

i think i need to clarify the problem here.

appWD.ActiveDocument.Label5y.Caption = "(Yes)"

this works because the label's name is "Label5y" but when i try to concatenate variables that produce the same name it does not work.

 

[Norie]

It's perfectly clear what the problem is.

The problem with finding a solution is that there doesn't appear to be a straightforward method to refer to an ActiveX on a Word document using it's name.

I can post code that will loop through all the ActiveX controls on the forms and looks for a label with a particular name, but you would need to repeat that for each label.