|
Removing Command Button
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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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 wrote: 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("Mac ro").Delete Please help. Thanks. |
Removing Command Button
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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . |
Removing Command Button
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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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" wrote: 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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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 "Accesshelp" wrote: 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" wrote: 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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson |
Removing Command Button
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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . |
Removing Command Button
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("Macr o").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. "FSt1" wrote: 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 "Accesshelp" wrote: 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" wrote: 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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson |
Removing Command Button
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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . |
Removing Command Button
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] On Wed, 5 May 2010 15:09:01 -0700, Accesshelp wrote: 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" wrote: 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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. |
Removing Command Button
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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Removing Command Button
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. "Chip Pearson" wrote: 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] On Wed, 5 May 2010 15:09:01 -0700, Accesshelp wrote: 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" wrote: 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("Comm andButton1").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" wrote: 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("Macr o").Delete Please help. Thanks. . |
Removing Command Button
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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
Removing Command Button
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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Removing Command Button
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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
Removing Command Button
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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Removing Command Button
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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . |
Removing Command Button
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] On Thu, 6 May 2010 19:56:01 -0700, Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . |
Removing Command Button
Just to add to Chip's response:
You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: 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 wrote: 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("Mac ro").Delete Please help. Thanks. . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Removing Command Button
Good morning Chip,
Thank you very much for your patience and continuing to help me. I changed my sub procedure to "Workbook_BeforeClose" from "Auto_Close". When I tested it, somehow, the code in the Workbook_BeforeClose is not executed. Therefore, the button is still not deleted from the Standard toolbar. Am I doing something wrong? Below please find the 3 sub procedures that I current have and have tested on, and all 3 subs are in General Module. Should the Workbook_BeforeClose in ThisWorkbook? Please take a look up all 3 procedures to see where I did wrong. Thanks again. Private Sub Auto_Open() Dim nBar As Variant Dim nCon As Variant Workbooks ("Excel Macro File.xls").Windows(1).Visible = False 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 Workbook_BeforeClose() Dim C As Office.CommandBarControl On Error Resume Next MsgBox "Can you see this message?", vbCritical, "Can you see me now?" Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Do Until C Is Nothing C.Delete Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop MsgBox "Goodbye....", vbInformation, "Exiting...." End 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 "Chip Pearson" wrote: 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] On Thu, 6 May 2010 19:56:01 -0700, Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. |
Removing Command Button
Good morning Dave,
As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: Try identifying the control with a Tag parameter: With nCon .BeginGroup = True .Style = msoButtonCaption |
Removing Command Button
I don't have a guess if you don't share your current version of the code.
Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: Try identifying the control with a Tag parameter: With nCon .BeginGroup = True .Style = msoButtonCaption -- Dave Peterson |
Removing Command Button
Chip,
Thanks for providing the information on Workbook_***, and please excuse my lack of knowledge in the area. Now I have the following code in ThisWorkbook: Private Sub Workbook_BeforeClose() Dim C As Office.CommandBarControl On Error Resume Next MsgBox "Can you see this message?", vbCritical, "Can you see me now?" Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Do Until C Is Nothing C.Delete Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop MsgBox "Goodbye....", vbInformation, "Exiting...." End Sub The problem that I am having now is if I press F5 (Run Sub/User Form) in VBAProject on "Macro" sub, the code in Workbook_BeforeClose executes, and the button gets deleted from the Standard toolbar. On the other hand, if I press the command button on the toolbar, macro executes the code all the way through to the code to delete the button, and the Excel window freezes/hangs, and the button does not get deleted. I have to manually close the Excel window. Thanks. "Chip Pearson" wrote: on, and all 3 subs are in General Module. Should the Workbook_BeforeClose in ThisWorkbook? The Workbook_Open and Workbook_BeforeClose procedures MUST reside in the ThisWorkbook code module, not a regular code module. For any event, VBA looks for code only in the object module attached to the object that triggers the event. Therefore, all Workbook_* events must be in ThisWorkbook, and all Worksheet_* events must be in the appropriate Sheet's code module. If the event code is elsewhere, VBA will not find it and thus not execute it. See http://www.cpearson.com/Excel/Events.aspx for much more info about events. If you want, you can email me the workbook and I'll have a look at it. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Fri, 7 May 2010 06:59:01 -0700, Accesshelp wrote: Good morning Chip, Thank you very much for your patience and continuing to help me. I changed my sub procedure to "Workbook_BeforeClose" from "Auto_Close". When I tested it, somehow, the code in the Workbook_BeforeClose is not executed. Therefore, the button is still not deleted from the Standard toolbar. Am I doing something wrong? Below please find the 3 sub procedures that I current have and have tested on, and all 3 subs are in General Module. Should the Workbook_BeforeClose in ThisWorkbook? Please take a look up all 3 procedures to see where I did wrong. Thanks again. Private Sub Auto_Open() Dim nBar As Variant Dim nCon As Variant Workbooks ("Excel Macro File.xls").Windows(1).Visible = False 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 Workbook_BeforeClose() Dim C As Office.CommandBarControl On Error Resume Next MsgBox "Can you see this message?", vbCritical, "Can you see me now?" Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Do Until C Is Nothing C.Delete Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop MsgBox "Goodbye....", vbInformation, "Exiting...." End 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 "Chip Pearson" wrote: 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] On Thu, 6 May 2010 19:56:01 -0700, Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: |
Removing Command Button
This worked fine for me -- all this code goes into a General module:
Option Explicit Sub auto_open() Dim nBar As CommandBar Dim nCon As CommandBarButton 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 = "MyTag" '<<<< ADDED End With End Sub Sub Auto_Close() MsgBox "auto_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" End If End Sub And if you wanted to use the Workbook events, all this code goes in the ThisWorkbook module: Option Explicit Private Sub Workbook_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Set nBar = Application.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 = "MyTag" '<<<< ADDED End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "before_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub But this code still goes into a General module: Option Explicit Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="CloseMe" End If End Sub =========== This was the question that Chip asked yesterday -- was the button trying to destroy itself? Since it can't do that, you can have code that says: Wait a second, then delete it using a different procedure (that .ontime stuff). Dave Peterson wrote: I don't have a guess if you don't share your current version of the code. Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: 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("Macr o").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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. "Chip Pearson" wrote: Try identifying the control with a Tag parameter: With nCon .BeginGroup = True .Style = msoButtonCaption -- Dave Peterson -- Dave Peterson |
Removing Command Button
Dave,
I updated my code with your code from below for General module, and I made some minor changes. The change that I made was I only took the following two lines from "Macro" Sub: Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" After I made the update, the macro worked perfectly. It deleted the button from the toolbar without any freeze/delay. I think the code from above fixes the delay and forces not to have any delay. If I may, I would like to ask you some questions. Without the code from above, why would you think there is a freeze/delay in deleting the button? In addition, why do we need an additional sub to close the macro file? Why can't we have a code to close the macro file within Macro sub? Thank you very much for your time and patience. "Dave Peterson" wrote: This worked fine for me -- all this code goes into a General module: Option Explicit Sub auto_open() Dim nBar As CommandBar Dim nCon As CommandBarButton 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 = "MyTag" '<<<< ADDED End With End Sub Sub Auto_Close() MsgBox "auto_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" End If End Sub And if you wanted to use the Workbook events, all this code goes in the ThisWorkbook module: Option Explicit Private Sub Workbook_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Set nBar = Application.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 = "MyTag" '<<<< ADDED End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "before_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub But this code still goes into a General module: Option Explicit Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="CloseMe" End If End Sub =========== This was the question that Chip asked yesterday -- was the button trying to destroy itself? Since it can't do that, you can have code that says: Wait a second, then delete it using a different procedure (that .ontime stuff). Dave Peterson wrote: I don't have a guess if you don't share your current version of the code. Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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 |
Removing Command Button
I don't see any difference (besides indenting) for those two lines. What change
did you make? The problem was deleting the control from the code that the control is running. That was Chip's point in that other post. So once the procedures were separated (using that .ontime stuff), the problem went away. Accesshelp wrote: Dave, I updated my code with your code from below for General module, and I made some minor changes. The change that I made was I only took the following two lines from "Macro" Sub: Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" After I made the update, the macro worked perfectly. It deleted the button from the toolbar without any freeze/delay. I think the code from above fixes the delay and forces not to have any delay. If I may, I would like to ask you some questions. Without the code from above, why would you think there is a freeze/delay in deleting the button? In addition, why do we need an additional sub to close the macro file? Why can't we have a code to close the macro file within Macro sub? Thank you very much for your time and patience. "Dave Peterson" wrote: This worked fine for me -- all this code goes into a General module: Option Explicit Sub auto_open() Dim nBar As CommandBar Dim nCon As CommandBarButton 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 = "MyTag" '<<<< ADDED End With End Sub Sub Auto_Close() MsgBox "auto_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" End If End Sub And if you wanted to use the Workbook events, all this code goes in the ThisWorkbook module: Option Explicit Private Sub Workbook_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Set nBar = Application.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 = "MyTag" '<<<< ADDED End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "before_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub But this code still goes into a General module: Option Explicit Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="CloseMe" End If End Sub =========== This was the question that Chip asked yesterday -- was the button trying to destroy itself? Since it can't do that, you can have code that says: Wait a second, then delete it using a different procedure (that .ontime stuff). Dave Peterson wrote: I don't have a guess if you don't share your current version of the code. Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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 -- Dave Peterson |
Removing Command Button
Chip,
I want to thank you and Dave very much for your time and patience for helping me with my problem. That was a long ride. You and Dave have helped me solved the problem. In addition, I have learned a lot from you guys. Thanks again. Have a great weekend! "Chip Pearson" wrote: on, and all 3 subs are in General Module. Should the Workbook_BeforeClose in ThisWorkbook? The Workbook_Open and Workbook_BeforeClose procedures MUST reside in the ThisWorkbook code module, not a regular code module. For any event, VBA looks for code only in the object module attached to the object that triggers the event. Therefore, all Workbook_* events must be in ThisWorkbook, and all Worksheet_* events must be in the appropriate Sheet's code module. If the event code is elsewhere, VBA will not find it and thus not execute it. See http://www.cpearson.com/Excel/Events.aspx for much more info about events. If you want, you can email me the workbook and I'll have a look at it. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Fri, 7 May 2010 06:59:01 -0700, Accesshelp wrote: Good morning Chip, Thank you very much for your patience and continuing to help me. I changed my sub procedure to "Workbook_BeforeClose" from "Auto_Close". When I tested it, somehow, the code in the Workbook_BeforeClose is not executed. Therefore, the button is still not deleted from the Standard toolbar. Am I doing something wrong? Below please find the 3 sub procedures that I current have and have tested on, and all 3 subs are in General Module. Should the Workbook_BeforeClose in ThisWorkbook? Please take a look up all 3 procedures to see where I did wrong. Thanks again. Private Sub Auto_Open() Dim nBar As Variant Dim nCon As Variant Workbooks ("Excel Macro File.xls").Windows(1).Visible = False 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 Workbook_BeforeClose() Dim C As Office.CommandBarControl On Error Resume Next MsgBox "Can you see this message?", vbCritical, "Can you see me now?" Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Do Until C Is Nothing C.Delete Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop MsgBox "Goodbye....", vbInformation, "Exiting...." End 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 "Chip Pearson" wrote: 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] On Thu, 6 May 2010 19:56:01 -0700, Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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. "Dave Peterson" wrote: |
Removing Command Button
Dave,
The followings are what I have now: Private Sub Auto_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Workbooks ("Excel Macro File.xls").Windows(1).Visible = False 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop End 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 Application.OnTime EarliestTime:=Now, Procedu="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedu="CloseMe" End Sub Private Sub CloseMe() ThisWorkbook.Close SaveChanges:=False End Sub The only big changes that I made were having CloseMe sub and the following two lines in Macro sub: Application.OnTime EarliestTime:=Now, Procedu="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedu="CloseMe" I think the above two lines made it worked. Thank you and Chip again very much for your time and patience. That was a long ride. Have a great weekend! "Dave Peterson" wrote: I don't see any difference (besides indenting) for those two lines. What change did you make? The problem was deleting the control from the code that the control is running. That was Chip's point in that other post. So once the procedures were separated (using that .ontime stuff), the problem went away. Accesshelp wrote: Dave, I updated my code with your code from below for General module, and I made some minor changes. The change that I made was I only took the following two lines from "Macro" Sub: Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" After I made the update, the macro worked perfectly. It deleted the button from the toolbar without any freeze/delay. I think the code from above fixes the delay and forces not to have any delay. If I may, I would like to ask you some questions. Without the code from above, why would you think there is a freeze/delay in deleting the button? In addition, why do we need an additional sub to close the macro file? Why can't we have a code to close the macro file within Macro sub? Thank you very much for your time and patience. "Dave Peterson" wrote: This worked fine for me -- all this code goes into a General module: Option Explicit Sub auto_open() Dim nBar As CommandBar Dim nCon As CommandBarButton 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 = "MyTag" '<<<< ADDED End With End Sub Sub Auto_Close() MsgBox "auto_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" End If End Sub And if you wanted to use the Workbook events, all this code goes in the ThisWorkbook module: Option Explicit Private Sub Workbook_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Set nBar = Application.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 = "MyTag" '<<<< ADDED End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "before_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub But this code still goes into a General module: Option Explicit Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="CloseMe" End If End Sub =========== This was the question that Chip asked yesterday -- was the button trying to destroy itself? Since it can't do that, you can have code that says: Wait a second, then delete it using a different procedure (that .ontime stuff). Dave Peterson wrote: I don't have a guess if you don't share your current version of the code. Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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.) |
Removing Command Button
I don't see the any difference in the CloseMe sub and I still don't see any
difference in those two ontime lines. But glad you have it working. Have a good weekend your own self <bg. Accesshelp wrote: Dave, The followings are what I have now: Private Sub Auto_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Workbooks ("Excel Macro File.xls").Windows(1).Visible = False 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MacroTag ") Loop End 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 Application.OnTime EarliestTime:=Now, Procedu="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedu="CloseMe" End Sub Private Sub CloseMe() ThisWorkbook.Close SaveChanges:=False End Sub The only big changes that I made were having CloseMe sub and the following two lines in Macro sub: Application.OnTime EarliestTime:=Now, Procedu="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedu="CloseMe" I think the above two lines made it worked. Thank you and Chip again very much for your time and patience. That was a long ride. Have a great weekend! "Dave Peterson" wrote: I don't see any difference (besides indenting) for those two lines. What change did you make? The problem was deleting the control from the code that the control is running. That was Chip's point in that other post. So once the procedures were separated (using that .ontime stuff), the problem went away. Accesshelp wrote: Dave, I updated my code with your code from below for General module, and I made some minor changes. The change that I made was I only took the following two lines from "Macro" Sub: Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" After I made the update, the macro worked perfectly. It deleted the button from the toolbar without any freeze/delay. I think the code from above fixes the delay and forces not to have any delay. If I may, I would like to ask you some questions. Without the code from above, why would you think there is a freeze/delay in deleting the button? In addition, why do we need an additional sub to close the macro file? Why can't we have a code to close the macro file within Macro sub? Thank you very much for your time and patience. "Dave Peterson" wrote: This worked fine for me -- all this code goes into a General module: Option Explicit Sub auto_open() Dim nBar As CommandBar Dim nCon As CommandBarButton 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 = "MyTag" '<<<< ADDED End With End Sub Sub Auto_Close() MsgBox "auto_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="Auto_Close" Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ procedu="CloseMe" End If End Sub And if you wanted to use the Workbook events, all this code goes in the ThisWorkbook module: Option Explicit Private Sub Workbook_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Set nBar = Application.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 = "MyTag" '<<<< ADDED End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) MsgBox "before_close" 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 = Nothing Set C = Application.CommandBars.FindControl(Tag:="MyTag") Loop End Sub But this code still goes into a General module: Option Explicit Sub CloseMe() ThisWorkbook.Close savechanges:=False End Sub Sub RunMacro() MsgBox "hi" Dim resp As Long resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) If resp = vbYes Then Application.OnTime earliesttime:=Now, procedu="CloseMe" End If End Sub =========== This was the question that Chip asked yesterday -- was the button trying to destroy itself? Since it can't do that, you can have code that says: Wait a second, then delete it using a different procedure (that .ontime stuff). Dave Peterson wrote: I don't have a guess if you don't share your current version of the code. Accesshelp wrote: Good morning Dave, As you instructed, I added the following code to the Macro sub (instead of after 'Workbooks("Excel Macro File.xls").Close False', I added before). With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With The code in Auto_Close did execute, but Excel just froze at the step of deleting the button. Do you know why? Thanks. "Dave Peterson" wrote: Just to add to Chip's response: You could run the auto_close procedure this way: Option Explicit Sub Macro() 'your code to do the checking 'then close it With ThisWorkbook .RunAutoMacros which:=xlAutoClose .Close savechanges:=False End With End Sub Sub auto_Close() MsgBox "auto_close" 'clean up the toolbar End Sub Accesshelp wrote: 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. "Dave Peterson" wrote: 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). Accesshelp wrote: 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. "Dave Peterson" wrote: 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 procedu 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 Peterson |
| All times are GMT +1. The time now is 08:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com