Command Button on worksheet

I have created a command button on a worksheet and have assigned a macro to
it. It works fine.
Is there a way I can access the caption of this button using VBA. I cant
find where this object fits within the commandbars structure.
I just dont know how to access it since it is not part of a toolbar.
Jack

Hi Jack,

If your command button was created from the Forms toolbar then you can
access the button caption in VBA using code that looks something like this:

ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Hello"
Range("A1").Select

Assumptions: The name of the command button is "Button 1" and the button is
on the currently active sheet. You can modify these items as necessary.

If the command button came from the Control Toolbox then modify the caption
via the button's Caption property.


"Jack" wrote:

> I have created a command button on a worksheet and have assigned a macro to
> it. It works fine.
> Is there a way I can access the caption of this button using VBA. I cant
> find where this object fits within the commandbars structure.
> I just dont know how to access it since it is not part of a toolbar.
> Jack
>
>
>

Are you trying to change the caption when you click on that button?

If yes, I used this macro that was assigned to a button from the Forms toolbar:

Option Explicit
Sub testme()
Dim myBTN As Button
Set myBTN = ActiveSheet.Buttons(Application.Caller)
myBTN.Caption = "Hi there" & vbLf & Now
End Sub

Each time I clicked on the button, the caption changed to include the current
time.

If you want to change the caption for any button, you can be explicit:

activesheet.buttons("Button 1").caption = "Whatever you want"




Jack wrote:
>
> I have created a command button on a worksheet and have assigned a macro to
> it. It works fine.
> Is there a way I can access the caption of this button using VBA. I cant
> find where this object fits within the commandbars structure.
> I just dont know how to access it since it is not part of a toolbar.
> Jack

--

Dave Peterson

Thanks Paul,
I just never considered that this object was a shape object. I tried what
you did and it did work. I am amazed at the code because I would have never
got there nor find any reference to this type of code.
I have several questions:
Evidently the shape object doesn't have a text property or any direct way to
change the text?.
also you have used CHARACTERS which I Just read is an object itself. Does
the font property of it allow me to change the color of the text that I
want.?
Thanks for you help
Jack





"Paul Mathews" <[email protected]> wrote in message
news:[email protected]...
> Hi Jack,
>
> If your command button was created from the Forms toolbar then you can
> access the button caption in VBA using code that looks something like
> this:
>
> ActiveSheet.Shapes("Button 1").Select
> Selection.Characters.Text = "Hello"
> Range("A1").Select
>
> Assumptions: The name of the command button is "Button 1" and the button
> is
> on the currently active sheet. You can modify these items as necessary.
>
> If the command button came from the Control Toolbox then modify the
> caption
> via the button's Caption property.
>
>
> "Jack" wrote:
>
>> I have created a command button on a worksheet and have assigned a macro
>> to
>> it. It works fine.
>> Is there a way I can access the caption of this button using VBA. I cant
>> find where this object fits within the commandbars structure.
>> I just dont know how to access it since it is not part of a toolbar.
>> Jack
>>
>>
>>

Hi again Jack, you are correct about modifying the character properties of
the text on the button. For example, if you wanted to increase the font size
to 12 and the font colour to red, you'd modify the code to:

ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Hello"
With Selection.Characters(Start:=1, Length:=8).Font
.Size = 12
.ColorIndex = 3
End With
Range("A1").Select

Note that you can choose to modify specific bits of the text string since
you can specify the font properties from a particular start point and apply
it for a particular length of the (sub)string.


"Jack" wrote:

> Thanks Paul,
> I just never considered that this object was a shape object. I tried what
> you did and it did work. I am amazed at the code because I would have never
> got there nor find any reference to this type of code.
> I have several questions:
> Evidently the shape object doesn't have a text property or any direct way to
> change the text?.
> also you have used CHARACTERS which I Just read is an object itself. Does
> the font property of it allow me to change the color of the text that I
> want.?
> Thanks for you help
> Jack
>
>
>
>
>
> "Paul Mathews" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Jack,
> >
> > If your command button was created from the Forms toolbar then you can
> > access the button caption in VBA using code that looks something like
> > this:
> >
> > ActiveSheet.Shapes("Button 1").Select
> > Selection.Characters.Text = "Hello"
> > Range("A1").Select
> >
> > Assumptions: The name of the command button is "Button 1" and the button
> > is
> > on the currently active sheet. You can modify these items as necessary.
> >
> > If the command button came from the Control Toolbox then modify the
> > caption
> > via the button's Caption property.
> >
> >
> > "Jack" wrote:
> >
> >> I have created a command button on a worksheet and have assigned a macro
> >> to
> >> it. It works fine.
> >> Is there a way I can access the caption of this button using VBA. I cant
> >> find where this object fits within the commandbars structure.
> >> I just dont know how to access it since it is not part of a toolbar.
> >> Jack
> >>
> >>
> >>
>
>
>