Removing Command Button
in [Programming]
|
Prev: Lookup Table
Next: remove duplicates
From: Accesshelp on 5 May 2010 14:57 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: Chip Pearson on 5 May 2010 15:40 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.
From: Accesshelp on 5 May 2010 16:39 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. > . >
From: FSt1 on 5 May 2010 16:55 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 5 May 2010 18:09
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. |