Rename embeded Button in a sheet

[fadib]

Hi Guys,
I am trying to add a commandbutton to a worksheet on a selected cell, and change its name using VBA.
The name is from an input message.

I need your help, cause recording only worked in inserting the commandbutton, but couldn't rename it, or even add it at the selected cell.

When I looked at the recorded macro, I noticed that the position is described by Left, Top, Height and Width.

/How can I get the location of a selected cell. Because if I do, I can work it out with variables and solve the positioning issue.

and how can I perform the renaming of both the Caption and the actual button name?

[leigao84]

Selection.Address should return the absolute address in string format

What type of commandbutton is it? Form Buttons and ActiveX buttons are inherently different.

[Rob342]

Hi
Try this not well tested, insert a name into A1

[VBA]

Sub Macro1()
'
' Macro1 Macro

Set Mytxt = Worksheets("sheet1").Range("A1") ' what apprears in A1 replaces the text in the button
ActiveSheet.Shapes("Button 1").Select 'change "Button 1" to "CommandButton1" if button = activeX
Selection.Characters.Text = Mytxt
With Selection.Characters(Start:=1, Length:=4).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
'.ThemeColor = 2
'.TintAndShade = 0
'.ThemeFont = xlThemeFontNone
End With

End Sub
[/VBA]

Rob

[cplindem]

Hi all,
Sorry to revive an old thread, but I'm new to VBA and have a question regarding the above code. I was able to use this code to rename one of my buttons, but I don't understand why the "select" is necessary. Elsewhere I always see people recommending to not use "selection". So...I tried to rewrite this:

    Sheets("Summaries").Shapes("Button1").Select
    Selection.Characters.Text = "tester"

Into this:

    Sheets("Summaries").Shapes("Button1").Characters.Text = "tester"

But it returns a 438 error. Why is that?

[lecxe]

Hi

Try:

Sheets("Summaries").Buttons("Button1").Text = "tester"

[cplindem]

Returns error 1004

[lecxe]

That will happen if the name of the button is misspelled. Please confirm, for ex., that there is no spaces in the name. An example: "Button 1" instead of "Button1".

Also I assumed that this is a Forms button, not an ActiveX button.

[lecxe]

Also a simple test, that changes the first button and does not use the name:

Sheets("Summaries").Buttons(1).Text = "tester"

[cplindem]

I just double-checked and it is named "Button1" (no spaces). As for the type of button, it is actually just a shape that I assigned a macro to. This is why I was using "Shapes" in my code rather than "Buttons".

However, your test code does not work (for me), even if I replace "Buttons(1)" with "Shapes(1)".

[lecxe]

Well, you named the shape "Button1" and I wrongly assumed it was a real button.

If it's a shape like a rectangle or an oval, try:

Worksheets("Summaries").Shapes("Button1").OLEFormat.Object.Text = "Tester"

[cplindem]

That did it. Thank you.

So just to understand, I need to use "OLEFormat.Object" because I'm not actually selecting the shape?

[lecxe]

Well, the shape is not what you are after, it's the object inside it. As you may already know a Shape object acts like a container. It can contain objects of a very different nature like an Oval, Chart, a Form Control, an ActiveX control, etc.

When you select the shape excel selects the object inside the shape. You can then access it directly.
If you do not select the shape, you must access the object inside it yourself.

[cplindem]

Hmmm, interesting.
I am finding there are many "hidden" things about VBA you just have to know (learn) in order to get it to function. Not very intuitive.

Thank you once again for the explanation.

[lecxe]

I'm glad it helped. Thanks for the feedback.