How can the caption of a (button) shape (not cmd) be changed from VBA

[AnExpertNovice]

On Excel worksheets I use the button shape from the Forms toolbar and assign
macros.

Properties exist such as Name and AlternativeText but I am unable to find a
way to change what the user sees from VBA. There is no text, caption,
label, etc. property.

Since it can be edited manually it must be changeable from code, although it
may require Windows API to accomplish it.

Any suggestions?

 

[Jim Thomlinson]

The easiest solution (IMO) would be to change your buttons from Forms buttons
(shapes) to Control Toolbox Buttons (actual buttons with properties and
methods). These buttons are easy to reference and have the all of the
properties you require. It will be a bit of work, but it should not be too
bad. The Code for these buttons will be embedded right in the sheet. You just
need to add one line of code for each button to call your macros...

 

[Nathaniel Gibson]

ActiveSheet.Shapes("nameofshape").Characters.text = "Your Text Here"

Nathaniel

 

[Gee-off]

What is VBA anyway?

The easiest solution (IMO) would be to change your buttons from Forms buttons
(shapes) to Control Toolbox Buttons (actual buttons with properties and
methods). These buttons are easy to reference and have the all of the
properties you require. It will be a bit of work, but it should not be too
bad. The Code for these buttons will be embedded right in the sheet. You just
need to add one line of code for each button to call your macros...

 

[AnExpertNovice]

Nathaniel,

Thanks! Possibly because I'm using Excel 2002 the format actually turned
out to be.
ActiveSheet.Shapes("nameofshape").TextFrame.Characters.Text =
"testing"
But that minor change was easy with your help.


Jim,

Thanks for responding, but having code embedded in the worksheet rather than
a module is exactly what I prefer to avoid

 

[AnExpertNovice]

Visual Basic for Application. A very nice programming language. The
simplest use would be for creating user functions. A more complex use would
be for manipulating another application such as Access or Outlook.

 

[Gee-off]

Thank you.

Visual Basic for Application. A very nice programming language. The
simplest use would be for creating user functions. A more complex use would
be for manipulating another application such as Access or Outlook.

 

[Tom Ogilvy]

If it is a button from the forms toolbar, it does have a caption property.
However, you have to treat it as a button to use it. If you want to treat
it as a shape:

ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "ABCD"


but as a button it is

ActiveSheet.buttons("Button 1").Caption = "ABCDEF"


A button is a hidden object in the object browser, so if you right click on
the browser and select show hidden members, you will be able to see the
properties.

Unlike Jim, unless there are special properties you need to work with, I
don't see any reason to go to control toobox toolbar commandbuttons.