Variable as Object

squeebles

New Member
Joined
Feb 13, 2006
Messages
11
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
How have you created these labels?

Are they ActiveX labels?
 

squeebles

New Member
Joined
Feb 13, 2006
Messages
11

ADVERTISEMENT

You want something like:

appWD.ActiveDocument.labels(labely).Caption

i will try this.

result:

Object doesn't support this property or method.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 1, 2011
Messages
51

ADVERTISEMENT

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

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

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2006
Messages
11
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:
Code:
Selection.InlineShapes.AddOLEControl ClassType:="Forms.Label.1"
btw thank you both for your responses
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top