remove button

[leungkong]

I have used below code to add a button when workbook open.
I want to remove it when work close.
But the button name is "Button 1".
How can I change the name when I create the button? Thanks.

Worksheets("MIRO").Buttons.Add(240, 15, 80, 16).Select
With Selection
.OnAction = "break"
.Characters.Text = "Break"
End With
With Selection.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With

 

[Rick Rothstein \(MVP - VB\)]

You can just name it right after the With Selection statement...

Worksheets("MIRO").Buttons.Add(240, 15, 80, 16).Select
With Selection
.Name = "NewButtonName"
.OnAction = "break"
.Characters.Text = "Break"
With .Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = xlAutomatic
End With
End With

I posted the whole routine because I wanted to show you another way to
handle your With statements. Instead of ending the first With statement in
order to start your next one, which simply a property of the object you had
in your first With statement, you can just nest the continued With statement
instead. Notice the dot in front of the Characters(...) property call....
that just tells the second With statement to reference the first With
statement's object while inside the second With statement. When the second
(nested) With statement ends, the first With statement is back in
"control"... meaning and dotted properties you place after the inner With
statement's End With will reference the first With statement's object (in
this case, Selection). Once you get the hang of this, I think you will find
this a more convenient way to handle With statements.

Now, as for deleting the button (which I named NewButtonName in my above
example), just execute this code...

Worksheets("MIRO").Buttons("NewButtonName").Delete

substituting the name you give to the button when you create it.

Rick

 

[JLGWhiz]

You cannot rename that particular button. However, using the OLEObject
button type, you can.

Sub addCmdButn()
Sheets("MIRO").Shapes.AddOLEObject Left:=240, Top:=15, _
Width:=80, Height:=16, ClassType:="Forms.CommandButton.1"
With Worksheets(2).OLEObjects("CommandButton1").Object
.Name = "YourChoice"
End With
End Sub

 

[leungkong]

Thanks Rick. You not only help me to solve this problem but also help me to
open a door for how to handle with statement.
Best Regards
Kong

 

[Rick Rothstein \(MVP - VB\)]

Ignore my posting about the button's Name and follow what JLGWhiz has told
you. I could swear when I tested the code I posted, the button got named and
I was able to use that name to delete it later on... I just know it worked
then, but I cannot no longer get to work any more, so obviously I was
mistaken.

However, what I posted about the nested With statements was accurate and you
can follow my instruction on that part of what I posted.

Rick

 

[Rick Rothstein \(MVP - VB\)]

See my other post in this thread... it seems something is wrong with what I
told you about naming the button.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Never mind... you **CAN** follow the advice I gave you in my posting... it
**DOES** work. What happened is I was working on Sheet1 with my test code,
but before I copy/pasted the line for deleting the button, I changed **my**
Sheet1 to your **MIRO** in my test code... and left it in. So in my
subsequent test, I created the button on Sheet1 and was trying to delete if
from MIRO. Once I changed MIRO back to Sheet1, the code worked fine again.
I'm really very sorry for any confusion my series of messages may have
caused you.

To repeat... there is nothing wrong with the code I posted initially... it
all works in the way I indicated in that first message.

Rick

 

[JLGWhiz]

I stand corrected. You can rename the button with code, but you cannot
access its properties to rename it, as you can the OLEObject type command
buttons.

 

[Rick Rothstein \(MVP - VB\)]

Ignore this message.... there is nothing wrong with what I told you about
naming the button... see my follow up message in the main thread.

Rick