Removing Command Button
in [Programming]
|
Prev: Lookup Table
Next: remove duplicates
From: Accesshelp on 6 May 2010 10:58 Good morning Dave, Thanks for continuing to help me. In the general module, I inserted the following code, and the command button is still on the Standard toolbar when the macro file is closed. Private Sub Auto_Close() Application.CommandBars("Standard").Controls("Macro").Delete End Sub "Macro" is the name (and caption) of command button. Did I miss something? Please help. Thanks. "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 > > > > > <Accesshelp(a)discussions.microsoft.com> 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("Macro").Delete > > > > > > > > > > > >Please help. Thanks. > > > > > . > > > > > > > > > > > -- > > > > > > Dave Peterson > > > . > > > > > -- > > Dave Peterson > . >
From: Chip Pearson on 6 May 2010 12:09 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 <Accesshelp(a)discussions.microsoft.com> 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("CommandButton1").Delete >> >> i usually change the default names of all my controls. for command buttons, >> i usually use CB1, CB2 ect. might mean less typing later on. >> but different strokes for different folks. we all have our preferences. >> >> Regards >> FSt1 >> >> >> "Accesshelp" 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("Macro").Delete >> > >> > Please help. Thanks.
From: Dave Peterson on 6 May 2010 12:20 If you used Chip's code to add the commandbutton, then use Chip's code to delete it. Any chance you had multiple buttons on that Standard toolbar and you deleted just one of them? If that's the case, then delete the others manually (see the previous message) before you start testing again. Accesshelp wrote: > > Good morning Dave, > > Thanks for continuing to help me. > > In the general module, I inserted the following code, and the command button > is still on the Standard toolbar when the macro file is closed. > > Private Sub Auto_Close() > Application.CommandBars("Standard").Controls("Macro").Delete > End Sub > > "Macro" is the name (and caption) of command button. > > Did I miss something? Please help. Thanks. > > "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 > > > > > > <Accesshelp(a)discussions.microsoft.com> 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("Macro").Delete > > > > > > > > > > > > > >Please help. Thanks. > > > > > > . > > > > > > > > > > > > > > -- > > > > > > > > Dave Peterson > > > > . > > > > > > > > -- > > > > Dave Peterson > > . > > -- Dave Peterson
From: Accesshelp on 6 May 2010 13:55 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 > <Accesshelp(a)discussions.microsoft.com> 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("CommandButton1").Delete > >> > >> i usually change the default names of all my controls. for command buttons, > >> i usually use CB1, CB2 ect. might mean less typing later on. > >> but different strokes for different folks. we all have our preferences. > >> > >> Regards > >> FSt1 > >> > >> > >> "Accesshelp" 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("Macro").Delete > >> > > >> > Please help. Thanks. > . >
From: Accesshelp on 6 May 2010 14:20
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("Macro").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 > > > > > > > <Accesshelp(a)discussions.microsoft.com> 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("Macro").Delete > > > > > > > > > > > > > > > >Please help. Thanks. > > > > > > > . > > > > > > > > > > > > > > > > > -- > > > > > > > > > > Dave Peterson > > > > > . > > > > > > > > > > > -- > > > > > > Dave Peterson > > > . > > > > > -- > > Dave Peterson > . > |