Chip,

Thanks for providing the information on Workbook_***, and please excuse my
lack of knowledge in the area.

Now I have the following code in ThisWorkbook:

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

The problem that I am having now is if I press F5 (Run Sub/User Form) in
VBAProject on "Macro" sub, the code in Workbook_BeforeClose executes, and the
button gets deleted from the Standard toolbar. On the other hand, if I press
the command button on the toolbar, macro executes the code all the way
through to the code to delete the button, and the Excel window freezes/hangs,
and the button does not get deleted. I have to manually close the Excel
window.

Thanks.








"Chip Pearson" wrote:


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








On Fri, 7 May 2010 06:59:01 -0700, Accesshelp
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
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.)

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: