I don't see the any difference in the CloseMe sub and I still don't see any
difference in those two ontime lines.

But glad you have it working. Have a good weekend your own self <bg.

Accesshelp wrote:

Dave,

The followings are what I have now:

Private Sub Auto_Open()

Dim nBar As CommandBar
Dim nCon As CommandBarButton

Workbooks ("Excel Macro File.xls").Windows(1).Visible = False

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MacroTag"
End With

End Sub

Private Sub Auto_Close()

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MacroTag ")
Do Until C Is Nothing
C.Delete
Set C = Nothing
Set C = Application.CommandBars.FindControl(Tag:="MacroTag ")
Loop

End Sub

Private Sub Macro()

Dim PROMPT As String

PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion,
Title:="Macro Title")

If PROMPT = vbNo Then
MsgBox "The macro is terminated.",vbInformation,"Macro Title"

Else
'The code to execute

End If

Application.OnTime EarliestTime:=Now, Procedu="Auto_Close"
Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1),
Procedu="CloseMe"
End Sub

Private Sub CloseMe()
ThisWorkbook.Close SaveChanges:=False
End Sub

The only big changes that I made were having CloseMe sub and the following
two lines in Macro sub:

Application.OnTime EarliestTime:=Now, Procedu="Auto_Close"
Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 1),
Procedu="CloseMe"

I think the above two lines made it worked.

Thank you and Chip again very much for your time and patience. That was a
long ride. Have a great weekend!

"Dave Peterson" wrote:

I don't see any difference (besides indenting) for those two lines. What change
did you make?

The problem was deleting the control from the code that the control is running.
That was Chip's point in that other post.

So once the procedures were separated (using that .ontime stuff), the problem
went away.



Accesshelp wrote:

Dave,

I updated my code with your code from below for General module, and I made
some minor changes. The change that I made was I only took the following two
lines from "Macro" Sub:

Application.OnTime earliesttime:=Now, procedu="Auto_Close"
Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _
procedu="CloseMe"

After I made the update, the macro worked perfectly. It deleted the button
from the toolbar without any freeze/delay. I think the code from above fixes
the delay and forces not to have any delay.

If I may, I would like to ask you some questions.

Without the code from above, why would you think there is a freeze/delay in
deleting the button? In addition, why do we need an additional sub to close
the macro file? Why can't we have a code to close the macro file within
Macro sub?

Thank you very much for your time and patience.

"Dave Peterson" wrote:

This worked fine for me -- all this code goes into a General module:

Option Explicit
Sub auto_open()
Dim nBar As CommandBar
Dim nCon As CommandBarButton

Set nBar = CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

End Sub
Sub Auto_Close()
MsgBox "auto_close"

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Nothing
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop
End Sub
Sub CloseMe()
ThisWorkbook.Close savechanges:=False
End Sub
Sub RunMacro()
MsgBox "hi"
Dim resp As Long

resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo)

If resp = vbYes Then
Application.OnTime earliesttime:=Now, procedu="Auto_Close"
Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), _
procedu="CloseMe"
End If
End Sub

And if you wanted to use the Workbook events, all this code goes in the
ThisWorkbook module:

Option Explicit
Private Sub Workbook_Open()

Dim nBar As CommandBar
Dim nCon As CommandBarButton

Set nBar = Application.CommandBars("Standard")
nBar.Visible = True
Set nCon = nBar.Controls.Add(Type:=msoControlButton, Temporary:=True)
With nCon
.BeginGroup = True
.Style = msoButtonCaption
.Caption = "Macro"
.OnAction = "RunMacro"
.Tag = "MyTag" '<<<< ADDED
End With

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "before_close"

Dim C As Office.CommandBarControl
On Error Resume Next
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Do Until C Is Nothing
C.Delete
Set C = Nothing
Set C = Application.CommandBars.FindControl(Tag:="MyTag")
Loop

End Sub

But this code still goes into a General module:

Option Explicit
Sub CloseMe()
ThisWorkbook.Close savechanges:=False
End Sub
Sub RunMacro()
MsgBox "hi"
Dim resp As Long

resp = MsgBox(Prompt:="clean up?", Buttons:=vbYesNo)

If resp = vbYes Then
Application.OnTime earliesttime:=Now, procedu="CloseMe"
End If
End Sub

===========

This was the question that Chip asked yesterday -- was the button trying to
destroy itself?

Since it can't do that, you can have code that says: Wait a second, then delete
it using a different procedure (that .ontime stuff).

Dave Peterson wrote:

I don't have a guess if you don't share your current version of the code.

Accesshelp wrote:

Good morning Dave,

As you instructed, I added the following code to the Macro sub (instead of
after 'Workbooks("Excel Macro File.xls").Close False', I added before).

With ThisWorkbook
.RunAutoMacros which:=xlAutoClose
.Close savechanges:=False
End With

The code in Auto_Close did execute, but Excel just froze at the step of
deleting the button.

Do you know why?

Thanks.

"Dave Peterson" wrote:

Just to add to Chip's response:

You could run the auto_close procedure this way:

Option Explicit
Sub Macro()

'your code to do the checking

'then close it
With ThisWorkbook
.RunAutoMacros which:=xlAutoClose
.Close savechanges:=False
End With
End Sub
Sub auto_Close()
MsgBox "auto_close"
'clean up the toolbar
End Sub


Accesshelp wrote:

Dave,

I inserted a msgbox at the beginning and ending of Auto_Close, and none of
the message boxes showed up. Apparently, the code in Auto_Close did not get
executed.

Below is the structure of my "Macro" (one of the 3 subs, besides Auto_Open
and Auto_Close) sub:

Private Sub Macro()

Dim PROMPT As String

PROMPT = Msgbox(PROMPT:="Message", Buttons:=vbYesNo + vbQuestion,
Title:="Macro Title")

If PROMPT = vbNo Then
MsgBox "The macro is terminated.",vbInformation,"Macro Title"

Else
'The code to execute

End If

Workbooks("Excel Macro File.xls").Close False

End Sub

I know that the "Excel Macro File.xls" closes because I tested by adding a
msgbox right above the Workbooks("Excel Macro File.xls").Close False code.
Somehow, the code in Auto_Close is completely ignored.

By looking at the structure of Macro sub from above, can you think of any
reason that would triggle not to read the the code in Auto_Close?

In addition to the testing from above, I also did another testing. I added
the code in Auto_Close as part of "Macro" sub, and the code was added right
before "Workbooks("Excel Macro File.xls").Close False". When I executed the
"Macro" code, the Excel window just froze.

Am I doing something wrong?

Thank you again very much for continuing to help me.

"Dave Peterson" wrote:

So when you added the msgbox to the auto_close procedure, did you see it when
the macro workbook closed?

Are you positive that you used the same tag in both the Auto_Open and Auto_Close
procedures. Yeah, I know that you posted code that was correct. But sometimes
what's posted can be changed (ever so slightly).



Accesshelp wrote:

Dave,

First of all, all 3 subs are in general module.

Secondly, I followed your steps from your last post, and I did not see the
button on the Excel file (that I execute the macro on) after I closed and
reopened.

However, I do still see the button in the following situation.

I open the Excel file ("Excel Data File") for which I want to execute my
macro on. Then I open the macro file in the same Excel window, and the
button is created on the Standard toolbar after the macro file is opened. I
click on the button to run the macro, and the macro executes the code. After
the macro finishes with the execution, the macro file closes, and the "Excel
Data File" and the Excel window still open. At that time, the button should
remove/delete from the Standard toolbar, but it's not.

That is where I have a problem with.

Thank you very much for your patience and continuing to help me.

"Dave Peterson" wrote:

That code worked perfectly fine for me.

After you close the workbook, clean up that toolbar manually -- just to make
sure there's nothing wrong to start.

Then load your workbook with the macros.

Do you see the new button?

Close the workbook.

Did the button disappear?

ps.

Add a msgbox to the top of each procedu

msgbox "Auto_Open running"
and
msgbox "Auto_Close running"

Just to make sure that both are running when they should. (You do have them in
a General module, right? They don't belong in the ThisWorkbook module or behind
a worksheet.)


--

Dave Peterson