How can I programatically change the caption on a button?

[June Macleod]

Microsoft Excel 2003


I have created a button on a spreadsheet using the following code

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=147, Top:=27.75, Width:=145.5,
Height:= _
33).Select
ActiveSheet.Shapes("CommandButton1").Name = "cmdButton"

but when I try and set a caption for the button using:
ActiveSheet.Shapes("CommandButton1").caption = "Hello World" it tells me
that the
"Object doesn't support this object or method"

When I try and record a macro to change the caption I end up with:
ActiveSheet.Shapes("CommandButton1").Select
Selection.Verb Verb:=xlPrimary

which frankly, I don't understand.

Can anyone help me here and tell me how to apply a caption to my button.


Many thanks


June

 

[Ron de Bruin]

Hi June

Try this

ActiveSheet.OLEObjects("cmdButton").Object.Caption = "Hi there"

 

[Ron de Bruin]

In one step do it like this

Dim Ws As Worksheet
Dim Btn As OLEObject

With ActiveSheet
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Print workbook"
Btn.Name = "YourPrintButton"

 

[Don Wiss]

Microsoft Excel 2003

I have created a button on a spreadsheet using the following code

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=147, Top:=27.75, Width:=145.5, Height:= _
33).Select

Why are you creating a button from the Control Toolbox? Why not from the
Forms toolbox?

ActiveSheet.Shapes("CommandButton1").Name = "cmdButton"

but when I try and set a caption for the button using:
ActiveSheet.Shapes("CommandButton1").caption = "Hello World" it tells me
that the
"Object doesn't support this object or method"

That would work fine if you created a Forms button.

Don <www.donwiss.com> (e-mail link at home page bottom).