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"

--
Regards Ron de Bruin
http://www.rondebruin.nl


"June Macleod" <junemacleod@btconnect.com> wrote in message news:Cs2dnfpVbPfCLeTZRVnyrg@bt.com...
> 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]

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"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"June Macleod" <junemacleod@btconnect.com> wrote in message news:Cs2dnfpVbPfCLeTZRVnyrg@bt.com...
> 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
>
>

[Don Wiss]

On Sun, 28 May 2006, June Macleod <junemacleod@btconnect.com> wrote:

>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).