Programmatically adding buttons to a worksheet (Shape Object)
 

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

Programmatically adding buttons to a worksheet (Shape Object)
 

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)

"Phil" wrote in message
...
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....

thanks

Philip

Programmatically adding buttons to a worksheet (Shape Object)
 

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).C aption = "test

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

thanks anyway..

Philip

Programmatically adding buttons to a worksheet (Shape Object)
 

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

"Phil" wrote in message
...
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).C aption = "test"

this is ok:
SheetName.OLEObjects(SheetName.OLEObjects.Count).O bject.Caption = "test"

thanks anyway...

Philip