Programmatically adding buttons to a worksheet (Shape Object)

[Guest]

Hi

I want to add forms control buttons (shapes) to a worksheet dynamically. Trouble is, although I can change the name and so on I cannot find out where the caption property is..

I am doing this in code
Dim xBtnShape As Shap

Set xBtnShape = Distribution.Shapes.AddFormControl(xlButtonControl, Range("D1").Left, Range("D1").Top, Range("e1").Left - Range("d1").Left, Range("e2").Top - Range("e1").Top
xBtnShape.Name = "cmdClassXXX
xBtnShape.Visible = msoCTru

<<<< END CODE <<<

so how can I change the caption text of the new button to what I want instead of the defaukt...which is like Button 10 or something...

thanks

Philip

 

[Bob Phillips]

Philip,

Try this

xBtnShape.Select
Selection.Characters.Text = "Bob"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi,

I want to add forms control buttons (shapes) to a worksheet dynamically.

Trouble is, although I can change the name and so on I cannot find out where
the caption property is...

I am doing this in code:

Dim xBtnShape As Shape

Set xBtnShape = Distribution.Shapes.AddFormControl(xlButtonControl,

Range("D1").Left, Range("D1").Top, Range("e1").Left - Range("d1").Left,
Range("e2").Top - Range("e1").Top)

xBtnShape.Name = "cmdClassXXX"
xBtnShape.Visible = msoCTrue

<<<< END CODE <<<<

so how can I change the caption text of the new button to what I want

instead of the defaukt...which is like Button 10 or something....

 

[Guest]

Thanks Bob, but that doesn't work

We found the answer, it's meant to be referenced as an Object in the OLEObjects collection..

so while this is no good
SheetName.OLEObjects(SheetName.OLEObjects.Count).Caption = "test

this is ok
SheetName.OLEObjects(SheetName.OLEObjects.Count).Object.Caption = "test

thanks anyway..

Philip

 

[Bob Phillips]

As far as I am aware, OleObjects is the controls toolbox collection, whereas
your original code create a forms button. I don't see the two bits workintg
together.

Bob