Workbook_BeforeClose does not work on Exit

[josnah]

I'm trying to delete my custom toolbar using the following:

' Personal.xls, "ThisWokbook"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Macros").Visible = False
Application.CommandBars("Macros").Delete
End Sub

However it only works if I close the file manually.
It does not work if I exit MS Excel without closing the file first o
if it is a hidden file.

Is it meant to work only if the file is closed manually? :confused

 

[jdstein11]

Why are you trying to delete a toolbar in Personal.xls before closing?
would think you would want your toolbar available whenever Excel i
launched.

BTW, are you using a toolbar that is "attached" to Personal.xls? Wh
not overwrite Excel.xlb instead

 

[josnah]

The toolbar is attached in the file "Personal.xls"
I'm trying to delete the toolbar so that the Excel.xlb is no
"bloated".

But even then, does *Workbook_BeforeClose * work on hidden files o
when u quit Excel without closing the file? I'm really puzzled on wh
it doesn't work.

~ VBA Newbi

 

[JE McGimpsey]

Are you creating this toolbar using VBA code? In that case you needn't
delete the toolbar, just declare it as temporary when you create it:

Commandbars.Add(Name:="MyBar", temporary:=True)

then it won't be saved in your .xlb file.

The Workbook_BeforeClose routine *should* be firing. Have you set a
breakpoint in the sub to see if it's being called, then step through
your code?

 

[josnah]

I did create it using VBA but the toolbar is attached to the Workbook.
can work around that.

but why is it that the Workbook_BeforeClose doesn't work?
Shouldn't it?

I did try to set a breakpoint in the sub to see if it's being called.

But it only works on a visible file which I manually close.
I have tried both at home & office. Still doesn't work

Anyone else with this problem. I'll give up if its just me...

btw thanks jdstein11 & JE McGimpsey for ur posts

 

[JE McGimpsey]

If the event itself isn't firing, I'd look for code that sets
Application.EnableEvents to false.

 

[Jon Peltier]

If you create the toolbar using VBA, you shouldn't be attaching it anywhere.

An unattached toolbar should delete fine (although as JE states,
deleting is redundant if you use Temporary:=True). But it reloads when
you start Excel again, because the personal.xls file is opened, and the
VBA will redraw the toolbar. You are drawing it on startup, aren't you?

I used to have problems with the _BeforeClose event procedure in my
personal.xls in Excel 97. I figured out a different way to do whatever
it was (years ago, I can't remember), and got on with it.

- Jon

 

[josnah]

I renamed the file "Personal.xls" to "Personal1.xls".

It works now! The toolbar gets deleted once I quit XL even if the fil
is still open or hidden.

So the problem is that when the file "Personal.xls" is not close
manually first before quitting XL, the Workbook_BeforeClose procedur
does not fire.
That is the Workbook_BeforeClose procedure does not work o
"Personal.xls" file located at XLStart folder. (I still don't no why..
)

However it works fine on any file that is not named "Personal.xls"

Thank you all for your replies..

 

[Jon Peltier]

I never thought of that. What a funny workaround.

- Jon