Workbook_BeforeClose does not work on Exit

  • Thread starter Thread starter josnah
  • Start date Start date
J

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
 
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
 
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.

~ :confused: VBA Newbi
 
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?
 
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
 
If the event itself isn't firing, I'd look for code that sets
Application.EnableEvents to false.
 
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
 
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..
:confused: )

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

Thank you all for your replies..
 
Back
Top