Programmatically adding buttons to a worksheet (Shape Object)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 said:
Hi,

I want to add forms control buttons (shapes) to a worksheet dynamically.
Click to expand...
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,
Click to expand...
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
Click to expand...
instead of the defaukt...which is like Button 10 or something....
 
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
 
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
 
Back
Top