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

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


"leungkong" wrote in message
...
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

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)" wrote:

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


"leungkong" wrote in message
...
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

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

Rick


"leungkong" wrote in message
...
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)" wrote:

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


"leungkong" wrote in message
...
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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
See my other post in this thread... it seems something is wrong with what
I told you about naming the button.

Rick


"leungkong" wrote in message
...
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)" wrote:

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


"leungkong" wrote in message
...
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

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)" wrote in
message ...
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


"leungkong" wrote in message
...
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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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)" wrote in
message ...
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


"leungkong" wrote in message
...
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

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" wrote:

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

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.

"leungkong" wrote:

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