Change Caption of Button

Hi,

I just added a button to one of my worksheets this way:

ActiveSheet.Buttons.Add(100, 300, 180, 15)

Now I would like to change the caption of the button, but don't know how to
do it. Help and MSDN-Reference don't cover this object or I cannot find it.

I can add an event to my buttons, but cannot change the caption...

ActiveSheet.Shapes(3).OnAction = "SwitchTo_"
ActiveSheet.Shapes(3).Name = "Caption" ' doesn't work

Any suggestions?

Many thanks
Alex

Use

ActiveSheet.Shapes(3).Caption = "Caption"

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


"Alexander" <Alexander@discussions.microsoft.com> wrote in message news:2252E952-AF3A-40C4-BB33-DAFA764B3B4F@microsoft.com...
> Hi,
>
> I just added a button to one of my worksheets this way:
>
> ActiveSheet.Buttons.Add(100, 300, 180, 15)
>
> Now I would like to change the caption of the button, but don't know how to
> do it. Help and MSDN-Reference don't cover this object or I cannot find it.
>
> I can add an event to my buttons, but cannot change the caption...
>
> ActiveSheet.Shapes(3).OnAction = "SwitchTo_"
> ActiveSheet.Shapes(3).Name = "Caption" ' doesn't work
>
> Any suggestions?
>
> Many thanks
> Alex
>

Hi Alexander,

Here's one way - name the button as you create it:

Sheet1.Buttons.Add(100, 300, 180, 15).Name = "btnTest"
Sheet1.Buttons("btnTest").Caption = "Test"

If you want to access the button from the Shapes collection, I think you'll
have to use this method:

ActiveSheet.Shapes(3).OLEFormat.Object.Caption = "Test"

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Alexander wrote:
> Hi,
>
> I just added a button to one of my worksheets this way:
>
> ActiveSheet.Buttons.Add(100, 300, 180, 15)
>
> Now I would like to change the caption of the button, but don't know
> how to do it. Help and MSDN-Reference don't cover this object or I
> cannot find it.
>
> I can add an event to my buttons, but cannot change the caption...
>
> ActiveSheet.Shapes(3).OnAction = "SwitchTo_"
> ActiveSheet.Shapes(3).Name = "Caption" ' doesn't work
>
> Any suggestions?
>
> Many thanks
> Alex

Try this:

Sub test()
With ActiveSheet.Buttons.Add(100, 300, 180, 15)
.Caption = "whatever"
End With
End Sub



"Alexander" wrote:

> Hi,
>
> I just added a button to one of my worksheets this way:
>
> ActiveSheet.Buttons.Add(100, 300, 180, 15)
>
> Now I would like to change the caption of the button, but don't know how to
> do it. Help and MSDN-Reference don't cover this object or I cannot find it.
>
> I can add an event to my buttons, but cannot change the caption...
>
> ActiveSheet.Shapes(3).OnAction = "SwitchTo_"
> ActiveSheet.Shapes(3).Name = "Caption" ' doesn't work
>
> Any suggestions?
>
> Many thanks
> Alex
>