Set Properties of TextBox in VBA

[Don Rouse]

I am adding a textbox and trying to set its properties using VBA. I get the
box but cannot figureout haw to set the properties.

Here is part of my code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=500, Top:=690, Width:=30, Height:=18). _
Select

With Selection
.Placement = xlMoveAndSize
.PrintObject = False
End With

' I want to set the properties of textbox1 here,
' but do not know how to do it. Need to set LinkedCell and Value.

Your assistance is appreciated. Thank you.

 

[Nigel]

ActiveSheet.OLEObjects("TextBox1").LinkedCell = "A1"
ActiveSheet.OLEObjects("Textbox1").Object.Value = "Text"

 

[Bob Phillips]

Better not to assume the name allocated

With ActiveSheet.OLEObjects
Set tb = .Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=500, Top:=690, Width:=30,
Height:=18)
tb.Name = "Bob"
tb.LinkedCell = "A1"
tb.Object.Value = "Text"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Rouse]

Bob,

Thank you. It works fine.
--
Don

Better not to assume the name allocated

With ActiveSheet.OLEObjects
Set tb = .Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=500, Top:=690, Width:=30,
Height:=18)
tb.Name = "Bob"
tb.LinkedCell = "A1"
tb.Object.Value = "Text"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Rouse]

Nigel,

Thank you. Your code works fine.