Macro Code for Work_BeforeClose MsgBox

[yari130]

Hello. Very simple, I need a macro that will allow me to do basically what the save before exit msgBox shows. I need this as a safety before closing the program to apply the filter for the next user.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Message to M H Beck; PLEASE APPLY FILTER TO YE"),vbOkCancel
End Sub

Ok will let me go on to the save msgbox and exit.

Cancel will return me to my workbook to Apply the filter.

If possible, I would like the buttons to read as follows:

Ok = It's Done

Cancel = Re-apply filter

Thank you for your help.

 

[xlbo]

You need API calls to change the messages on messagebox buttons - is it really worth it ??
Other than that, you can create a form with command buttons and make it LOOK like a messagebox - that way you can put whatever caption you like on there but again...is it worth it ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Enkay62]

Hi.
As far as I've understood you need to show a personalized dialog box instead of excel before-close alert. Is it?
If so, don't use msgboxes but create your own a form.
i.e. Userform1.
Add manually a label and type your personalized message.
Add manually two commandbuttons and type your caption.
Write your code in both click events of 2 comndbuttons.
Besides, if you want to prevent the user from exiting the form by clicking the "X", in the form mudule, after the 2 commandbuttons events, insert the Queryclose event as following.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
End Sub

Al last, write your code in workbook beforclose event adding

Application.DisplayAlerts=False
'(So Excel won't show it's alert screen)
Load Userform1


Hope this helps
Bye
Nick