Removing Command Button
in [Programming]
|
Prev: Lookup Table
Next: remove duplicates
From: Accesshelp on 7 May 2010 16:28 Dave, The followings are what I have now: Private Sub Auto_Open() Dim nBar As CommandBar Dim nCon As CommandBarButton Workbooks ("Excel Macro File.xls").Windows(1).Visible = False Set nBar = CommandBars("Standard") nBar.Visible = True Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) With nCon .BeginGroup = True .Style = msoButtonCaption .Caption = "Macro" .OnAction = "RunMacro" .Tag = "MacroTag" End With End Sub Private Sub Auto_Close() Dim C As Office.CommandBarControl On Error Resume Next Set C = Application.CommandBars.FindControl(Tag:="MacroTag") Do Until C Is Nothing C.Delete Set C = Nothing Set C = Application.CommandBars.FindControl(Tag:="MacroTag") Loop End Sub Private Sub Macro() Dim PROMPT As String PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion, Title:="Macro Title") If PROMPT = vbNo Then MsgBox "The macro is terminated.",vbInformation,"Macro Title" Else 'The code to execute End If Application.OnTime EarliestTime:=Now, Procedure:="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedure:="CloseMe" End Sub Private Sub CloseMe() ThisWorkbook.Close SaveChanges:=False End Sub The only big changes that I made were having CloseMe sub and the following two lines in Macro sub: Application.OnTime EarliestTime:=Now, Procedure:="Auto_Close" Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), Procedure:="CloseMe" I think the above two lines made it worked. Thank you and Chip again very much for your time and patience. That was a long ride. Have a great weekend! "Dave Peterson" wrote: > I don't see any difference (besides indenting) for those two lines. What change > did you make? > > The problem was deleting the control from the code that the control is running. > That was Chip's point in that other post. > > So once the procedures were separated (using that .ontime stuff), the problem > went away. > > > > Accesshelp wrote: > > > > Dave, > > > > I updated my code with your code from below for General module, and I made > > some minor changes. The change that I made was I only took the following two > > lines from "Macro" Sub: > > > > Application.OnTime earliesttime:=Now, procedure:="Auto_Close" > > Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ > > procedure:="CloseMe" > > > > After I made the update, the macro worked perfectly. It deleted the button > > from the toolbar without any freeze/delay. I think the code from above fixes > > the delay and forces not to have any delay. > > > > If I may, I would like to ask you some questions. > > > > Without the code from above, why would you think there is a freeze/delay in > > deleting the button? In addition, why do we need an additional sub to close > > the macro file? Why can't we have a code to close the macro file within > > Macro sub? > > > > Thank you very much for your time and patience. > > > > "Dave Peterson" wrote: > > > > > This worked fine for me -- all this code goes into a General module: > > > > > > Option Explicit > > > Sub auto_open() > > > Dim nBar As CommandBar > > > Dim nCon As CommandBarButton > > > > > > Set nBar = CommandBars("Standard") > > > nBar.Visible = True > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > With nCon > > > .BeginGroup = True > > > .Style = msoButtonCaption > > > .Caption = "Macro" > > > .OnAction = "RunMacro" > > > .Tag = "MyTag" '<<<< ADDED > > > End With > > > > > > End Sub > > > Sub Auto_Close() > > > MsgBox "auto_close" > > > > > > Dim C As Office.CommandBarControl > > > On Error Resume Next > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > Do Until C Is Nothing > > > C.Delete > > > Set C = Nothing > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > Loop > > > End Sub > > > Sub CloseMe() > > > ThisWorkbook.Close savechanges:=False > > > End Sub > > > Sub RunMacro() > > > MsgBox "hi" > > > Dim resp As Long > > > > > > resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) > > > > > > If resp = vbYes Then > > > Application.OnTime earliesttime:=Now, procedure:="Auto_Close" > > > Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ > > > procedure:="CloseMe" > > > End If > > > End Sub > > > > > > And if you wanted to use the Workbook events, all this code goes in the > > > ThisWorkbook module: > > > > > > Option Explicit > > > Private Sub Workbook_Open() > > > > > > Dim nBar As CommandBar > > > Dim nCon As CommandBarButton > > > > > > Set nBar = Application.CommandBars("Standard") > > > nBar.Visible = True > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > With nCon > > > .BeginGroup = True > > > .Style = msoButtonCaption > > > .Caption = "Macro" > > > .OnAction = "RunMacro" > > > .Tag = "MyTag" '<<<< ADDED > > > End With > > > > > > End Sub > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > > > > > MsgBox "before_close" > > > > > > Dim C As Office.CommandBarControl > > > On Error Resume Next > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > Do Until C Is Nothing > > > C.Delete > > > Set C = Nothing > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > Loop > > > > > > End Sub > > > > > > But this code still goes into a General module: > > > > > > Option Explicit > > > Sub CloseMe() > > > ThisWorkbook.Close savechanges:=False > > > End Sub > > > Sub RunMacro() > > > MsgBox "hi" > > > Dim resp As Long > > > > > > resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) > > > > > > If resp = vbYes Then > > > Application.OnTime earliesttime:=Now, procedure:="CloseMe" > > > End If > > > End Sub > > > > > > =========== > > > > > > This was the question that Chip asked yesterday -- was the button trying to > > > destroy itself? > > > > > > Since it can't do that, you can have code that says: Wait a second, then delete > > > it using a different procedure (that .ontime stuff). > > > > > > Dave Peterson wrote: > > > > > > > > I don't have a guess if you don't share your current version of the code. > > > > > > > > Accesshelp wrote: > > > > > > > > > > Good morning Dave, > > > > > > > > > > As you instructed, I added the following code to the Macro sub (instead of > > > > > after 'Workbooks("Excel Macro File.xls").Close False', I added before). > > > > > > > > > > With ThisWorkbook > > > > > .RunAutoMacros which:=xlAutoClose > > > > > .Close savechanges:=False > > > > > End With > > > > > > > > > > The code in Auto_Close did execute, but Excel just froze at the step of > > > > > deleting the button. > > > > > > > > > > Do you know why? > > > > > > > > > > Thanks. > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > Just to add to Chip's response: > > > > > > > > > > > > You could run the auto_close procedure this way: > > > > > > > > > > > > Option Explicit > > > > > > Sub Macro() > > > > > > > > > > > > 'your code to do the checking > > > > > > > > > > > > 'then close it > > > > > > With ThisWorkbook > > > > > > .RunAutoMacros which:=xlAutoClose > > > > > > .Close savechanges:=False > > > > > > End With > > > > > > End Sub > > > > > > Sub auto_Close() > > > > > > MsgBox "auto_close" > > > > > > 'clean up the toolbar > > > > > > End Sub > > > > > > > > > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > > > Dave, > > > > > > > > > > > > > > I inserted a msgbox at the beginning and ending of Auto_Close, and none of > > > > > > > the message boxes showed up. Apparently, the code in Auto_Close did not get > > > > > > > executed. > > > > > > > > > > > > > > Below is the structure of my "Macro" (one of the 3 subs, besides Auto_Open > > > > > > > and Auto_Close) sub: > > > > > > > > > > > > > > Private Sub Macro() > > > > > > > > > > > > > > Dim PROMPT As String > > > > > > > > > > > > > > PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion, > > > > > > > Title:="Macro Title") > > > > > > > > > > > > > > If PROMPT = vbNo Then > > > > > > > MsgBox "The macro is terminated.",vbInformation,"Macro Title" > > > > > > > > > > > > > > Else > > > > > > > 'The code to execute > > > > > > > > > > > > > > End If > > > > > > > > > > > > > > Workbooks("Excel Macro File.xls").Close False > > > > > > > > > > > > > > End Sub > > > > > > > > > > > > > > I know that the "Excel Macro File.xls" closes because I tested by adding a > > > > > > > msgbox right above the Workbooks("Excel Macro File.xls").Close False code. > > > > > > > Somehow, the code in Auto_Close is completely ignored. > > > > > > > > > > > > > > By looking at the structure of Macro sub from above, can you think of any > > > > > > > reason that would triggle not to read the the code in Auto_Close? > > > > > > > > > > > > > > In addition to the testing from above, I also did another testing. I added > > > > > > > the code in Auto_Close as part of "Macro" sub, and the code was added right > > > > > > > before "Workbooks("Excel Macro File.xls").Close False". When I executed the > > > > > > > "Macro" code, the Excel window just froze. > > > > > > > > > > > > > > Am I doing something wrong? > > > > > > > > > > > > > > Thank you again very much for continuing to help me. > > > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > > > So when you added the msgbox to the auto_close procedure, did you see it when > > > > > > > > the macro workbook closed? > > > > > > > > > > > > > > > > Are you positive that you used the same tag in both the Auto_Open and Auto_Close > > > > > > > > procedures. Yeah, I know that you posted code that was correct. But sometimes > > > > > > > > what's posted can be changed (ever so slightly). > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > > > > > > > Dave, > > > > > > > > > > > > > > > > > > First of all, all 3 subs are in general module. > > > > > > > > > > > > > > > > > > Secondly, I followed your steps from your last post, and I did not see the > > > > > > > > > button on the Excel file (that I execute the macro on) after I closed and > > > > > > > > > reopened. > > > > > > > > > > > > > > > > > > However, I do still see the button in the following situation. > > > > > > > > > > > > > > > > > > I open the Excel file ("Excel Data File") for which I want to execute my > > > > > > > > > macro on. Then I open the macro file in the same Excel window, and the > > > > > > > > > button is created on the Standard toolbar after the macro file is opened. I > > > > > > > > > click on the button to run the macro, and the macro executes the code. After > > > > > > > > > the macro finishes with the execution, the macro file closes, and the "Excel > > > > > > > > > Data File" and the Excel window still open. At that time, the button should > > > > > > > > > remove/delete from the Standard toolbar, but it's not. > > > > > > > > > > > > > > > > > > That is where I have a problem with. > > > > > > > > > > > > > > > > > > Thank you very much for your patience and continuing to help me. > > > > > > > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > > > > > > > That code worked perfectly fine for me. > > > > > > > > > > > > > > > > > > > > After you close the workbook, clean up that toolbar manually -- just to make > > > > > > > > > > sure there's nothing wrong to start. > > > > > > > > > > > > > > > > > > > > Then load your workbook with the macros. > > > > > > > > > > > > > > > > > > > > Do you see the new button? > > > > > > > > > > > > > > > > > > > > Close the workbook. > > > > > > > > > > > > > > > > > > > > Did the button disappear? > > > > > > > > > > > > > > > > > > > > ps. > > > > > > > > > > > > > > > > > > > > Add a msgbox to the top of each 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.) > > > > > > > > > >
From: Dave Peterson on 7 May 2010 17:54
I don't see the any difference in the CloseMe sub and I still don't see any difference in those two ontime lines. But glad you have it working. Have a good weekend your own self <bg>. Accesshelp wrote: > > Dave, > > The followings are what I have now: > > Private Sub Auto_Open() > > Dim nBar As CommandBar > Dim nCon As CommandBarButton > > Workbooks ("Excel Macro File.xls").Windows(1).Visible = False > > Set nBar = CommandBars("Standard") > nBar.Visible = True > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > With nCon > .BeginGroup = True > .Style = msoButtonCaption > .Caption = "Macro" > .OnAction = "RunMacro" > .Tag = "MacroTag" > End With > > End Sub > > Private Sub Auto_Close() > > Dim C As Office.CommandBarControl > On Error Resume Next > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > Do Until C Is Nothing > C.Delete > Set C = Nothing > Set C = Application.CommandBars.FindControl(Tag:="MacroTag") > Loop > > End Sub > > Private Sub Macro() > > Dim PROMPT As String > > PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion, > Title:="Macro Title") > > If PROMPT = vbNo Then > MsgBox "The macro is terminated.",vbInformation,"Macro Title" > > Else > 'The code to execute > > End If > > Application.OnTime EarliestTime:=Now, Procedure:="Auto_Close" > Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), > Procedure:="CloseMe" > End Sub > > Private Sub CloseMe() > ThisWorkbook.Close SaveChanges:=False > End Sub > > The only big changes that I made were having CloseMe sub and the following > two lines in Macro sub: > > Application.OnTime EarliestTime:=Now, Procedure:="Auto_Close" > Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1), > Procedure:="CloseMe" > > I think the above two lines made it worked. > > Thank you and Chip again very much for your time and patience. That was a > long ride. Have a great weekend! > > "Dave Peterson" wrote: > > > I don't see any difference (besides indenting) for those two lines. What change > > did you make? > > > > The problem was deleting the control from the code that the control is running. > > That was Chip's point in that other post. > > > > So once the procedures were separated (using that .ontime stuff), the problem > > went away. > > > > > > > > Accesshelp wrote: > > > > > > Dave, > > > > > > I updated my code with your code from below for General module, and I made > > > some minor changes. The change that I made was I only took the following two > > > lines from "Macro" Sub: > > > > > > Application.OnTime earliesttime:=Now, procedure:="Auto_Close" > > > Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ > > > procedure:="CloseMe" > > > > > > After I made the update, the macro worked perfectly. It deleted the button > > > from the toolbar without any freeze/delay. I think the code from above fixes > > > the delay and forces not to have any delay. > > > > > > If I may, I would like to ask you some questions. > > > > > > Without the code from above, why would you think there is a freeze/delay in > > > deleting the button? In addition, why do we need an additional sub to close > > > the macro file? Why can't we have a code to close the macro file within > > > Macro sub? > > > > > > Thank you very much for your time and patience. > > > > > > "Dave Peterson" wrote: > > > > > > > This worked fine for me -- all this code goes into a General module: > > > > > > > > Option Explicit > > > > Sub auto_open() > > > > Dim nBar As CommandBar > > > > Dim nCon As CommandBarButton > > > > > > > > Set nBar = CommandBars("Standard") > > > > nBar.Visible = True > > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > > With nCon > > > > .BeginGroup = True > > > > .Style = msoButtonCaption > > > > .Caption = "Macro" > > > > .OnAction = "RunMacro" > > > > .Tag = "MyTag" '<<<< ADDED > > > > End With > > > > > > > > End Sub > > > > Sub Auto_Close() > > > > MsgBox "auto_close" > > > > > > > > Dim C As Office.CommandBarControl > > > > On Error Resume Next > > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > > Do Until C Is Nothing > > > > C.Delete > > > > Set C = Nothing > > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > > Loop > > > > End Sub > > > > Sub CloseMe() > > > > ThisWorkbook.Close savechanges:=False > > > > End Sub > > > > Sub RunMacro() > > > > MsgBox "hi" > > > > Dim resp As Long > > > > > > > > resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) > > > > > > > > If resp = vbYes Then > > > > Application.OnTime earliesttime:=Now, procedure:="Auto_Close" > > > > Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _ > > > > procedure:="CloseMe" > > > > End If > > > > End Sub > > > > > > > > And if you wanted to use the Workbook events, all this code goes in the > > > > ThisWorkbook module: > > > > > > > > Option Explicit > > > > Private Sub Workbook_Open() > > > > > > > > Dim nBar As CommandBar > > > > Dim nCon As CommandBarButton > > > > > > > > Set nBar = Application.CommandBars("Standard") > > > > nBar.Visible = True > > > > Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True) > > > > With nCon > > > > .BeginGroup = True > > > > .Style = msoButtonCaption > > > > .Caption = "Macro" > > > > .OnAction = "RunMacro" > > > > .Tag = "MyTag" '<<<< ADDED > > > > End With > > > > > > > > End Sub > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > > > > > > > MsgBox "before_close" > > > > > > > > Dim C As Office.CommandBarControl > > > > On Error Resume Next > > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > > Do Until C Is Nothing > > > > C.Delete > > > > Set C = Nothing > > > > Set C = Application.CommandBars.FindControl(Tag:="MyTag") > > > > Loop > > > > > > > > End Sub > > > > > > > > But this code still goes into a General module: > > > > > > > > Option Explicit > > > > Sub CloseMe() > > > > ThisWorkbook.Close savechanges:=False > > > > End Sub > > > > Sub RunMacro() > > > > MsgBox "hi" > > > > Dim resp As Long > > > > > > > > resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo) > > > > > > > > If resp = vbYes Then > > > > Application.OnTime earliesttime:=Now, procedure:="CloseMe" > > > > End If > > > > End Sub > > > > > > > > =========== > > > > > > > > This was the question that Chip asked yesterday -- was the button trying to > > > > destroy itself? > > > > > > > > Since it can't do that, you can have code that says: Wait a second, then delete > > > > it using a different procedure (that .ontime stuff). > > > > > > > > Dave Peterson wrote: > > > > > > > > > > I don't have a guess if you don't share your current version of the code. > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > Good morning Dave, > > > > > > > > > > > > As you instructed, I added the following code to the Macro sub (instead of > > > > > > after 'Workbooks("Excel Macro File.xls").Close False', I added before). > > > > > > > > > > > > With ThisWorkbook > > > > > > .RunAutoMacros which:=xlAutoClose > > > > > > .Close savechanges:=False > > > > > > End With > > > > > > > > > > > > The code in Auto_Close did execute, but Excel just froze at the step of > > > > > > deleting the button. > > > > > > > > > > > > Do you know why? > > > > > > > > > > > > Thanks. > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > Just to add to Chip's response: > > > > > > > > > > > > > > You could run the auto_close procedure this way: > > > > > > > > > > > > > > Option Explicit > > > > > > > Sub Macro() > > > > > > > > > > > > > > 'your code to do the checking > > > > > > > > > > > > > > 'then close it > > > > > > > With ThisWorkbook > > > > > > > .RunAutoMacros which:=xlAutoClose > > > > > > > .Close savechanges:=False > > > > > > > End With > > > > > > > End Sub > > > > > > > Sub auto_Close() > > > > > > > MsgBox "auto_close" > > > > > > > 'clean up the toolbar > > > > > > > End Sub > > > > > > > > > > > > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > > > > > Dave, > > > > > > > > > > > > > > > > I inserted a msgbox at the beginning and ending of Auto_Close, and none of > > > > > > > > the message boxes showed up. Apparently, the code in Auto_Close did not get > > > > > > > > executed. > > > > > > > > > > > > > > > > Below is the structure of my "Macro" (one of the 3 subs, besides Auto_Open > > > > > > > > and Auto_Close) sub: > > > > > > > > > > > > > > > > Private Sub Macro() > > > > > > > > > > > > > > > > Dim PROMPT As String > > > > > > > > > > > > > > > > PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion, > > > > > > > > Title:="Macro Title") > > > > > > > > > > > > > > > > If PROMPT = vbNo Then > > > > > > > > MsgBox "The macro is terminated.",vbInformation,"Macro Title" > > > > > > > > > > > > > > > > Else > > > > > > > > 'The code to execute > > > > > > > > > > > > > > > > End If > > > > > > > > > > > > > > > > Workbooks("Excel Macro File.xls").Close False > > > > > > > > > > > > > > > > End Sub > > > > > > > > > > > > > > > > I know that the "Excel Macro File.xls" closes because I tested by adding a > > > > > > > > msgbox right above the Workbooks("Excel Macro File.xls").Close False code. > > > > > > > > Somehow, the code in Auto_Close is completely ignored. > > > > > > > > > > > > > > > > By looking at the structure of Macro sub from above, can you think of any > > > > > > > > reason that would triggle not to read the the code in Auto_Close? > > > > > > > > > > > > > > > > In addition to the testing from above, I also did another testing. I added > > > > > > > > the code in Auto_Close as part of "Macro" sub, and the code was added right > > > > > > > > before "Workbooks("Excel Macro File.xls").Close False". When I executed the > > > > > > > > "Macro" code, the Excel window just froze. > > > > > > > > > > > > > > > > Am I doing something wrong? > > > > > > > > > > > > > > > > Thank you again very much for continuing to help me. > > > > > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > > > > > So when you added the msgbox to the auto_close procedure, did you see it when > > > > > > > > > the macro workbook closed? > > > > > > > > > > > > > > > > > > Are you positive that you used the same tag in both the Auto_Open and Auto_Close > > > > > > > > > procedures. Yeah, I know that you posted code that was correct. But sometimes > > > > > > > > > what's posted can be changed (ever so slightly). > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Accesshelp wrote: > > > > > > > > > > > > > > > > > > > > Dave, > > > > > > > > > > > > > > > > > > > > First of all, all 3 subs are in general module. > > > > > > > > > > > > > > > > > > > > Secondly, I followed your steps from your last post, and I did not see the > > > > > > > > > > button on the Excel file (that I execute the macro on) after I closed and > > > > > > > > > > reopened. > > > > > > > > > > > > > > > > > > > > However, I do still see the button in the following situation. > > > > > > > > > > > > > > > > > > > > I open the Excel file ("Excel Data File") for which I want to execute my > > > > > > > > > > macro on. Then I open the macro file in the same Excel window, and the > > > > > > > > > > button is created on the Standard toolbar after the macro file is opened. I > > > > > > > > > > click on the button to run the macro, and the macro executes the code. After > > > > > > > > > > the macro finishes with the execution, the macro file closes, and the "Excel > > > > > > > > > > Data File" and the Excel window still open. At that time, the button should > > > > > > > > > > remove/delete from the Standard toolbar, but it's not. > > > > > > > > > > > > > > > > > > > > That is where I have a problem with. > > > > > > > > > > > > > > > > > > > > Thank you very much for your patience and continuing to help me. > > > > > > > > > > > > > > > > > > > > "Dave Peterson" wrote: > > > > > > > > > > > > > > > > > > > > > That code worked perfectly fine for me. > > > > > > > > > > > > > > > > > > > > > > After you close the workbook, clean up that toolbar manually -- just to make > > > > > > > > > > > sure there's nothing wrong to start. > > > > > > > > > > > > > > > > > > > > > > Then load your workbook with the macros. > > > > > > > > > > > > > > > > > > > > > > Do you see the new button? > > > > > > > > > > > > > > > > > > > > > > Close the workbook. > > > > > > > > > > > > > > > > > > > > > > Did the button disappear? > > > > > > > > > > > > > > > > > > > > > > ps. > > > > > > > > > > > > > > > > > > > > > > Add a msgbox to the top of each procedure: > > > > > > > > > > > > > > > > > > > > > > msgbox "Auto_Open running" > > > > > > > > > > > and > > > > > > > > > > > msgbox "Auto_Close running" > > > > > > > > > > > > > > > > > > > > > > Just to make sure that both are running when they should. (You do have them in > > > > > > > > > > > a General module, right? They don't belong in the ThisWorkbook module or behind > > > > > > > > > > > a worksheet.) > > > > > > > > > > > -- Dave Peterson |