Removing Command Button

[Accesshelp]

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.

 

[Chip Pearson]

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Accesshelp]

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.

.

 

[FSt1]

hi
wild guessing here but..
what is the name of the button. the button's caption may not necessarily be
the name of the button. by default excel give it the name 'commandbutton1'
and keeps count of them in the back ground asigning the next command button
name commandbutton2 and so on.
try
Application.CommandBars("Standard").Controls("CommandButton1").Delete

i usually change the default names of all my controls. for command buttons,
i usually use CB1, CB2 ect. might mean less typing later on.
but different strokes for different folks. we all have our preferences.

Regards
FSt1

 

[Accesshelp]

FSt1,

To be quite honest, I do not know the name of the button, and I do not know
how to give a name to the button that I created. The code in my original
post is all the code that I use to create the button.

Do you know how I can find out what the name of my button is? Is there an
alternative code without the button name?

Thanks.

 

[FSt1]

hi
i got confused and thought you were talking about a command button on the
sheet.
don't ask why. bad case of the stupids i guess.
but no different. right click the command bar. from the popup, click
customize.
right click the button. on the popup, about 3 from the top should be the
name. this is how excel ids the button. that was what chip was triing to do.
give it a alias name. try that. post back if no joy.

regards
FSt1

 

[Dave Peterson]

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.

.

 

[Accesshelp]

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Accesshelp]

FSt1,

Thanks for continuing to help. I found the name of command button that I
created after following your steps, and the name is the same name as Caption
"Macro".

Since I found the name, I tried your code from your first response as follows:

Application.CommandBars("Standard").Controls("Macro").Delete

When I tried it and executed the code, I received the following error:

Method 'Delete' of object'_CommandBarButton' failed

I used the code from above in "RunMacro" Sub, instead of Workbook_Close.

Thanks.

 

[Dave Peterson]

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Accesshelp]

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Chip Pearson]

Just out of curiosity, is the code that attempts to delete the command
button executed directly or indirectly by the code attached to the
command button? In other words, does the command button attempt to
delete itself? If so, you can't do that.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Dave Peterson]

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Accesshelp]

Chip,

Thanks for continuing to help me. I do not know whether the command button
attempts to delete itself. How do I find out?

Currently, I have the following code in Auto_Open and Auto_Close, and the
command button does not remove from the Standard toolbar when the macro file
closes (and I do not receive any error messages).

Auto_Open:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Auto_Close:

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

Thanks. Please help.

Just out of curiosity, is the code that attempts to delete the command
button executed directly or indirectly by the code attached to the
command button? In other words, does the command button attempt to
delete itself? If so, you can't do that.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

FSt1,

To be quite honest, I do not know the name of the button, and I do not know
how to give a name to the button that I created. The code in my original
post is all the code that I use to create the button.

Do you know how I can find out what the name of my button is? Is there an
alternative code without the button name?

Thanks.

.

 

[Accesshelp]

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Dave Peterson]

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedure:

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

:

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Accesshelp]

Dave,

First of all, all 3 subs are in general module.

Secondly, I followed your steps from your last post, and I did not see the
button on the Excel file (that I execute the macro on) after I closed and
reopened.

However, I do still see the button in the following situation.

I open the Excel file ("Excel Data File") for which I want to execute my
macro on. Then I open the macro file in the same Excel window, and the
button is created on the Standard toolbar after the macro file is opened. I
click on the button to run the macro, and the macro executes the code. After
the macro finishes with the execution, the macro file closes, and the "Excel
Data File" and the Excel window still open. At that time, the button should
remove/delete from the Standard toolbar, but it's not.

That is where I have a problem with.

Thank you very much for your patience and continuing to help me.

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedure:

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Accesshelp wrote:

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

:

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Dave Peterson]

So when you added the msgbox to the auto_close procedure, did you see it when
the macro workbook closed?

Are you positive that you used the same tag in both the Auto_Open and Auto_Close
procedures. Yeah, I know that you posted code that was correct. But sometimes
what's posted can be changed (ever so slightly).

Dave,

First of all, all 3 subs are in general module.

Secondly, I followed your steps from your last post, and I did not see the
button on the Excel file (that I execute the macro on) after I closed and
reopened.

However, I do still see the button in the following situation.

I open the Excel file ("Excel Data File") for which I want to execute my
macro on. Then I open the macro file in the same Excel window, and the
button is created on the Standard toolbar after the macro file is opened. I
click on the button to run the macro, and the macro executes the code. After
the macro finishes with the execution, the macro file closes, and the "Excel
Data File" and the Excel window still open. At that time, the button should
remove/delete from the Standard toolbar, but it's not.

That is where I have a problem with.

Thank you very much for your patience and continuing to help me.

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedure:

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

:

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Accesshelp wrote:

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

:

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Accesshelp]

Dave,

I inserted a msgbox at the beginning and ending of Auto_Close, and none of
the message boxes showed up. Apparently, the code in Auto_Close did not get
executed.

Below is the structure of my "Macro" (one of the 3 subs, besides Auto_Open
and Auto_Close) sub:

Private Sub Macro()

Dim PROMPT As String

PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion,
Title:="Macro Title")

If PROMPT = vbNo Then
MsgBox "The macro is terminated.",vbInformation,"Macro Title"

Else
'The code to execute

End If

Workbooks("Excel Macro File.xls").Close False

End Sub


I know that the "Excel Macro File.xls" closes because I tested by adding a
msgbox right above the Workbooks("Excel Macro File.xls").Close False code.
Somehow, the code in Auto_Close is completely ignored.

By looking at the structure of Macro sub from above, can you think of any
reason that would triggle not to read the the code in Auto_Close?

In addition to the testing from above, I also did another testing. I added
the code in Auto_Close as part of "Macro" sub, and the code was added right
before "Workbooks("Excel Macro File.xls").Close False". When I executed the
"Macro" code, the Excel window just froze.

Am I doing something wrong?

Thank you again very much for continuing to help me.

So when you added the msgbox to the auto_close procedure, did you see it when
the macro workbook closed?

Are you positive that you used the same tag in both the Auto_Open and Auto_Close
procedures. Yeah, I know that you posted code that was correct. But sometimes
what's posted can be changed (ever so slightly).

Dave,

First of all, all 3 subs are in general module.

Secondly, I followed your steps from your last post, and I did not see the
button on the Excel file (that I execute the macro on) after I closed and
reopened.

However, I do still see the button in the following situation.

I open the Excel file ("Excel Data File") for which I want to execute my
macro on. Then I open the macro file in the same Excel window, and the
button is created on the Standard toolbar after the macro file is opened. I
click on the button to run the macro, and the macro executes the code. After
the macro finishes with the execution, the macro file closes, and the "Excel
Data File" and the Excel window still open. At that time, the button should
remove/delete from the Standard toolbar, but it's not.

That is where I have a problem with.

Thank you very much for your patience and continuing to help me.

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedure:

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)

Accesshelp wrote:

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

:

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Accesshelp wrote:

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

:

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.

 

[Chip Pearson]

When working with Auto_Open and Auto_Close, keep in mind that these
procedures are NOT called when the workbook is opened or closed with
code. For example,

Sub AAA()
Workbooks.Open "C:\Book1.xls"
End Sub

With this code, the Auto_Open procedure in Book1.xls will not run.
Similarly,

Sub BBB()
Workbooks("Book1.xls").Close
End Sub

Here, the Auto_Close procedure in Book1.xls will not run.

However, the Workbook_Open and Workbook_BeforeClose procedures do run
when a file is opened or closed via code.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Dave,

I inserted a msgbox at the beginning and ending of Auto_Close, and none of
the message boxes showed up. Apparently, the code in Auto_Close did not get
executed.

Below is the structure of my "Macro" (one of the 3 subs, besides Auto_Open
and Auto_Close) sub:

Private Sub Macro()

Dim PROMPT As String

PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion,
Title:="Macro Title")

If PROMPT = vbNo Then
MsgBox "The macro is terminated.",vbInformation,"Macro Title"

Else
'The code to execute

End If

Workbooks("Excel Macro File.xls").Close False

End Sub


I know that the "Excel Macro File.xls" closes because I tested by adding a
msgbox right above the Workbooks("Excel Macro File.xls").Close False code.
Somehow, the code in Auto_Close is completely ignored.

By looking at the structure of Macro sub from above, can you think of any
reason that would triggle not to read the the code in Auto_Close?

In addition to the testing from above, I also did another testing. I added
the code in Auto_Close as part of "Macro" sub, and the code was added right
before "Workbooks("Excel Macro File.xls").Close False". When I executed the
"Macro" code, the Excel window just froze.

Am I doing something wrong?

Thank you again very much for continuing to help me.

So when you added the msgbox to the auto_close procedure, did you see it when
the macro workbook closed?

Are you positive that you used the same tag in both the Auto_Open and Auto_Close
procedures. Yeah, I know that you posted code that was correct. But sometimes
what's posted can be changed (ever so slightly).

Dave,

First of all, all 3 subs are in general module.

Secondly, I followed your steps from your last post, and I did not see the
button on the Excel file (that I execute the macro on) after I closed and
reopened.

However, I do still see the button in the following situation.

I open the Excel file ("Excel Data File") for which I want to execute my
macro on. Then I open the macro file in the same Excel window, and the
button is created on the Standard toolbar after the macro file is opened. I
click on the button to run the macro, and the macro executes the code. After
the macro finishes with the execution, the macro file closes, and the "Excel
Data File" and the Excel window still open. At that time, the button should
remove/delete from the Standard toolbar, but it's not.

That is where I have a problem with.

Thank you very much for your patience and continuing to help me.

:

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedure:

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)

Accesshelp wrote:

Dave,

As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and
when I tried it, the button is still on the Standard toolbar after the macro
file closes.

The followings are the code that I have in Auto_Open and Auto_Close,
respectively:

Private Sub Auto_Open()

Dim nBar As Variant
Dim nCon As Variant

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MacroTag")
Loop

End Sub

I do not have duplicate command buttons. There is only one button.

Thanks.

:

If you used Chip's code to add the commandbutton, then use Chip's code to delete
it.

Any chance you had multiple buttons on that Standard toolbar and you deleted
just one of them?

If that's the case, then delete the others manually (see the previous message)
before you start testing again.

Accesshelp wrote:

Good morning Dave,

Thanks for continuing to help me.

In the general module, I inserted the following code, and the command button
is still on the Standard toolbar when the macro file is closed.

Private Sub Auto_Close()
Application.CommandBars("Standard").Controls("Macro").Delete
End Sub

"Macro" is the name (and caption) of command button.

Did I miss something? Please help. Thanks.

:

It could be as simple as the name of your macro that you want to run when you
close that workbook.

If your procedure is in the ThisWorkbook module, it should look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

(there is no workbook_Close event that fires automatically.)

If the procedure is in a General module, then it should look like:
Sub Auto_Close()

====
You could test your code by running that workbook_close procedure yourself (but
remember, excel won't run it automatically!).

Accesshelp wrote:

Dave,

Thanks for your response.

Basically, I have an Excel file that is just dedicated for a macro, and the
Excel macro file will be used by users. The users will open the macro file
in the same window as an Excel file where the macro will execute the code.
The way I have designed is when the user opens the macro file, the macro file
will create the command button and will be hidden. When the user clicks on
the command button, the macro will execute its code. After the macro is
executed, the macro file will be closed, and the command button will remove
from the Standard toolbar. If the user does not click on the button and when
the Excel window is closed, the macro file will be closed and the button will
remove from the Standard toolbar.

The problem that I am having now is the button would not remove from the
toolbar.

In my Excel macro file, I have 3 Subs: Auto_Open, RunMacro and
Workbook_Close. The only code that I have in Auto_Open is a code to create
the command button "Macro" on the Standard toolbar, and the only code that I
would like to have in Workbook_Close is a code to remove the button from the
toolbar when the macro file closes.

As far as Runmacro, I use .OnAction = "RunMacro" in Auto_Open. When the
user clicks on the command button, OnAction calls up the RunMacro Sub and
executes the code in that Sub. At the end of RunMacro, I have a code to
close the macro Excel file.

I tried to use the code from Chip in Workbook_Close, and it did not remove
the button and did not seem to do anything.

I am sorry about the long message. I hope I have covered what you are
looking for.

What do you think I should do now?

Thanks.

:

I think it's time to share the code you used.

Did you create a separate sub to delete the control with that tag?
If yes, how did you run it?
And did you spell that Tag the same way in both routines?

Are you sure you're not looking at the control that was left over from previous
testing -- that one didn't have a tag.

I'd just delete it manually.

Inside excel:
Tools|Customize (just to see that dialog)
drag the offending control off the toolbar.



Accesshelp wrote:

Chip,

Thanks for the code.

I inserted a line for Tag in my Auto_Open sub and inserted the code to
delete the command button in my Workbook_Close sub. When I tried it, the
button did not delete from the Standard toolbar.

I am sure whether I did something wrong.

Thanks.

:

Try identifying the control with a Tag parameter:

With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

The text "MyTag" can be anything you want. Then, to delete the
controls, use

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

This will delete all controls whose Tag property is "MyTag".

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 5 May 2010 11:57:01 -0700, Accesshelp

Hello all,

I have a code that creates a command button when the Excel file opens. The
following is the code that I use:

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
End With

What I would like to do is to remove the above command button "Macro" when
the Excel file closes. I have tried to use the following code, and it did
not work.

Application.CommandBars("Standard").Controls("Macro").Delete

Please help. Thanks.
.