PDA

View Full Version : [SOLVED] Rename embeded Button in a sheet



fadib
05-24-2011, 10:46 AM
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?

:help

leigao84
05-24-2011, 01:38 PM
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
05-24-2011, 01:46 PM
Hi
Try this not well tested, insert a name into A1



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


Rob

cplindem
02-28-2014, 11:52 AM
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
02-28-2014, 12:05 PM
Hi

Try:


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

cplindem
02-28-2014, 12:35 PM
Returns error 1004

lecxe
02-28-2014, 01:49 PM
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
02-28-2014, 01:53 PM
Also a simple test, that changes the first button and does not use the name:


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

cplindem
02-28-2014, 01:59 PM
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
02-28-2014, 02:25 PM
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
02-28-2014, 03:01 PM
That did it. Thank you.

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

lecxe
02-28-2014, 04:49 PM
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
02-28-2014, 06:33 PM
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
03-01-2014, 02:30 AM
I'm glad it helped. Thanks for the feedback.