Solved: BeforeClose not firing on hidden personal.xls

[jazznaura]

Hi all,

The Workbook_BeforeClose event won't work on my hidden personal.xls. It works fine if the workbook is not hidden, is there an extra bit of code required or is it not possible.

thanks,

Private Sub DeleteAddedButtons()
' '---------------------------------------------------------------------------
' '//delete the main menu buttons\\
On Error Resume Next
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Refresh List").Delete
.Controls("Run Code").Delete
.Controls("Remove Mod").Delete
.Controls("Export Mod").Delete
.Controls("Import Mod").Delete
End With
'---------------------------------------------------------------------------
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteAddedButtons
Call MenuRemovePly
End Sub

[lucas]

What do you mean hidden?

Why would you open your personal.xls?

[jazznaura]

hi lucas,
i added buttons to the menu bar that run macros from my personal.xls, the buttons are added to the menu bar when the personal.xls in loaded when excel starts and i wish them to be removed when my personal.xls closes with excel. everything works fine if my personal workbook is visible but the closebefore won't run if personal workbook is not visible.
i hope that makes sense :)

[lucas]

What do you mean by hidden?

Also, personal runs every time a file is opened or a new file is created so why load and unload them? Make them permanent.

[jazznaura]

when i open excel my personal workbook is not visible, if i wish to see it i have to click window on menu bar and then unhide and select my file.

if its visible everything works fine, if not then beforeclose does not run.

i take copy of my personal file to work and use it there and don't wish to leave permanent buttons on a computer used by others. i could create 2 files but this is first time i've done this and was interested why the beforeclose event wasn't firing.

[lucas]

ah, I understand now.

I don't even use a personal.xls and I don't see any reason that your file has to be named such.

Why are you naming it personal.xls? Why not just use a normally named file and your code will work as you wish.

personal.xls won't run workbook close or open events on workbooks that you open in excel whether it is hidden or not.

[jazznaura]

not sure why or how but renaming it worked :)
thanks lucas.

[lucas]

You're welcome jazz, be sure to mark your thread solved using the thread tools at the top of the page.

That will keep others from reading the entire thread just to find it's been solved.