Removing Command Button
in [Programming]
|
Prev: Lookup Table
Next: remove duplicates
From: Dave Peterson on 7 May 2010 09:24 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 procedure: > > > > > > > > msgbox "Auto_Open running" > > > > and > > > > msgbox "Auto_Close running" > > > > > > > > Just to make sure that both are running when they should. (You do have them in > > > > a General module, right? They don't belong in the ThisWorkbook module or behind > > > > a worksheet.) > > > > > > > > Accesshelp wrote: > > > > > > > > > > Dave, > > > > > > > > > > As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and > > > > > when I tried it, the button is still on the Standard toolbar after the macro > > > > > file closes. > > > > > > > > > > The followings are the code that I have in Auto_Open and Auto_Close, > > > > > respectively: > > > > > > > > > > Private Sub Auto_Open() > > > > > > > > > > Dim nBar As Variant > > > > > Dim nCon As Variant > > > > > > > > > > Set nBar = CommandBars("Standard") > > > > > nBar.Visible = True > > > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > > > With nCon > > > > > .BeginGroup = True > > > > > .Style = msoButtonCaption > > > > > .Caption = "Macro" > > > > > .OnAction = "RunMacro" > > > > > .Tag = "MacroTag" > > > > > End With > > > > > > > > > > End Sub > > > > > > > > > > Private Sub Auto_Close() > > > > > > > > > > Dim C As Office.CommandBarControl > > > > > On Error Resume Next > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > Do Until C Is Nothing > > > > > C.Delete > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > Loop > > > > > > > > > > End Sub > > > > > > > > > > I do not have duplicate command buttons. There is only one button. > > > > > > > > > > Thanks. > > > > > > > > > > "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
From: Accesshelp on 7 May 2010 09:59 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 > <Accesshelp(a)discussions.microsoft.com> 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 procedure: > >> > > > >> > > msgbox "Auto_Open running" > >> > > and > >> > > msgbox "Auto_Close running" > >> > > > >> > > Just to make sure that both are running when they should. (You do have them in > >> > > a General module, right? They don't belong in the ThisWorkbook module or behind > >> > > a worksheet.) > >> > > > >> > > Accesshelp wrote: > >> > > > > >> > > > Dave, > >> > > > > >> > > > As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and > >> > > > when I tried it, the button is still on the Standard toolbar after the macro > >> > > > file closes. > >> > > > > >> > > > The followings are the code that I have in Auto_Open and Auto_Close, > >> > > > respectively: > >> > > > > >> > > > Private Sub Auto_Open() > >> > > > > >> > > > Dim nBar As Variant > >> > > > Dim nCon As Variant > >> > > > > >> > > > Set nBar = CommandBars("Standard") > >> > > > nBar.Visible = True > >> > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > >> > > > With nCon > >> > > > .BeginGroup = True > >> > > > .Style = msoButtonCaption > >> > > > .Caption = "Macro" > >> > > > .OnAction = "RunMacro" > >> > > > .Tag = "MacroTag" > >> > > > End With > >> > > > > >> > > > End Sub > >> > > > > >> > > > Private Sub Auto_Close() > >> > > > > >> > > > Dim C As Office.CommandBarControl > >> > > > On Error Resume Next > >> > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > >> > > > Do Until C Is Nothing > >> > > > C.Delete > >> > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > >> > > > Loop > >> > > > > >> > > > End Sub > >> > > > > >> > > > I do not have duplicate command buttons. There is only one button. > >> > > > > >> > > > Thanks. > >> > > > > >> > > > "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. > >> > > > > > > > > >
From: Accesshelp on 7 May 2010 10:24 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 procedure: > > > > > > > > > > msgbox "Auto_Open running" > > > > > and > > > > > msgbox "Auto_Close running" > > > > > > > > > > Just to make sure that both are running when they should. (You do have them in > > > > > a General module, right? They don't belong in the ThisWorkbook module or behind > > > > > a worksheet.) > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > Dave, > > > > > > > > > > > > As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and > > > > > > when I tried it, the button is still on the Standard toolbar after the macro > > > > > > file closes. > > > > > > > > > > > > The followings are the code that I have in Auto_Open and Auto_Close, > > > > > > respectively: > > > > > > > > > > > > Private Sub Auto_Open() > > > > > > > > > > > > Dim nBar As Variant > > > > > > Dim nCon As Variant > > > > > > > > > > > > Set nBar = CommandBars("Standard") > > > > > > nBar.Visible = True > > > > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > > > > With nCon > > > > > > .BeginGroup = True > > > > > > .Style = msoButtonCaption > > > > > > .Caption = "Macro" > > > > > > .OnAction = "RunMacro" > > > > > > .Tag = "MacroTag" > > > > > > End With > > > > > > > > > > > > End Sub > > > > > > > > > > > > Private Sub Auto_Close() > > > > > > > > > > > > Dim C As Office.CommandBarControl > > > > > > On Error Resume Next > > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > > Do Until C Is Nothing > > > > > > C.Delete > > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > > Loop > > > > > > > > > > > > End Sub > > > > > > > > > > > > I do not have duplicate command buttons. There is only one button. > > > > > > > > > > > > Thanks. > > > > > > > > > > > > "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
From: Chip Pearson on 7 May 2010 10:24 >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 chip(a)cpearson.com On Fri, 7 May 2010 06:59:01 -0700, Accesshelp <Accesshelp(a)discussions.microsoft.com> 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 >> <Accesshelp(a)discussions.microsoft.com> 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 procedure: >> >> > > >> >> > > msgbox "Auto_Open running" >> >> > > and >> >> > > msgbox "Auto_Close running" >> >> > > >> >> > > Just to make sure that both are running when they should. (You do have them in >> >> > > a General module, right? They don't belong in the ThisWorkbook module or behind >> >> > > a worksheet.) >> >> > > >> >> > > Accesshelp wrote: >> >> > > > >> >> > > > Dave, >> >> > > > >> >> > > > As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and >> >> > > > when I tried it, the button is still on the Standard toolbar after the macro >> >> > > > file closes. >> >> > > > >> >> > > > The followings are the code that I have in Auto_Open and Auto_Close, >> >> > > > respectively: >> >> > > > >> >> > > > Private Sub Auto_Open() >> >> > > > >> >> > > > Dim nBar As Variant >> >> > > > Dim nCon As Variant >> >> > > > >> >> > > > Set nBar = CommandBars("Standard") >> >> > > > nBar.Visible = True >> >> > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) >> >> > > > With nCon >> >> > > > .BeginGroup = True >> >> > > > .Style = msoButtonCaption >> >> > > > .Caption = "Macro" >> >> > > > .OnAction = "RunMacro" >> >> > > > .Tag = "MacroTag" >> >> > > > End With >> >> > > > >> >> > > > End Sub >> >> > > > >> >> > > > Private Sub Auto_Close() >> >> > > > >> >> > > > Dim C As Office.CommandBarControl >> >> > > > On Error Resume Next >> >> > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") >> >> > > > Do Until C Is Nothing >> >> > > > C.Delete >> >> > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") >> >> > > > Loop >> >> > > > >> >> > > > End Sub >> >> > > > >> >> > > > I do not have duplicate command buttons. There is only one button. >> >> > > > >> >> > > > Thanks. >> >> > > > >> >> > > > "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. >> >> > > > > > > > > >
From: Dave Peterson on 7 May 2010 10:36
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 procedure: > > > > > > > > > > > > msgbox "Auto_Open running" > > > > > > and > > > > > > msgbox "Auto_Close running" > > > > > > > > > > > > Just to make sure that both are running when they should. (You do have them in > > > > > > a General module, right? They don't belong in the ThisWorkbook module or behind > > > > > > a worksheet.) > > > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > > > Dave, > > > > > > > > > > > > > > As you instructed, I used Chip's code in both Auto_Open and Auto_Close, and > > > > > > > when I tried it, the button is still on the Standard toolbar after the macro > > > > > > > file closes. > > > > > > > > > > > > > > The followings are the code that I have in Auto_Open and Auto_Close, > > > > > > > respectively: > > > > > > > > > > > > > > Private Sub Auto_Open() > > > > > > > > > > > > > > Dim nBar As Variant > > > > > > > Dim nCon As Variant > > > > > > > > > > > > > > Set nBar = CommandBars("Standard") > > > > > > > nBar.Visible = True > > > > > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > > > > > With nCon > > > > > > > .BeginGroup = True > > > > > > > .Style = msoButtonCaption > > > > > > > .Caption = "Macro" > > > > > > > .OnAction = "RunMacro" > > > > > > > .Tag = "MacroTag" > > > > > > > End With > > > > > > > > > > > > > > End Sub > > > > > > > > > > > > > > Private Sub Auto_Close() > > > > > > > > > > > > > > Dim C As Office.CommandBarControl > > > > > > > On Error Resume Next > > > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > > > Do Until C Is Nothing > > > > > > > C.Delete > > > > > > > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > > > > > > > Loop > > > > > > > > > > > > > > End Sub > > > > > > > > > > > > > > I do not have duplicate command buttons. There is only one button. > > > > > > > > > > > > > > Thanks. > > > > > > > > > > > > > > "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 -- Dave Peterson |