How to use personal macros that will work each time any excel file closes

[evanmer]

I've writtenamacro in my whose purpose is to ask me, when I try to close thefile, whetheror not all tasks have been completed within that specificfile. Rather than addthis to the numerous excel files I get into each day, I'dlike to add it to mypersonal workbook. The problem I’m having is that thespecific code only workswhen I store the code in each unique file, as opposedto my personal workbook.Any recommendations on how to do this?

[COLOR=#222222][FONT=Verdana]Private Sub Workbook_BeforeClose(Cancel As Boolean) [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim ans AsVbMsgBoxResult [/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]ans =MsgBox("Are you sure you have done everything", vbYesNo + vbQuestion)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If ans = vbNoThen Cancel = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

 

[Logit]

.
Just to review you've added the macro correctly to your Personal.xlsb workbook :

https://support.office.com/en-us/ar...4db-b021-ae005a9bc790?ui=en-US&rs=en-US&ad=US

https://support.office.com/en-us/ar...381-97f0-6e4c3f5ee566?ui=en-US&rs=en-US&ad=US

https://superuser.com/questions/339694/can-i-make-an-excel-vba-macro-available-to-all-workbooks

 

[alansidman]

https://www.rondebruin.nl/win/personal.htm

 

[evanmer]

@Logit and @alansidman

Yes I’ve stored them properly in my personal workbook. Theissue I’m having is that the macros only work for when the user (me) commandsthem to run, as opposed to them running when certain actions happen; in my casethat’s when any excel file is closed.

 

[Fluff]

ADVERTISEMENT

Try this in your Personalworkbook

' needs to go in ThisWorkbook module
Public WithEvents App As Application


Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If MsgBox("Are you sure you have done everything", vbYesNo + vbQuestion) = vbNo Then Cancel = True
End Sub

Private Sub Workbook_Open()
   Set App = Application
End Sub

 

[evanmer]

@Fluff this worked great! The only thing I added in was a IF statement to have it only run on certain files. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback