Problems inserting MessageBox with option to cancel closing file
I'm trying to create a prompt to remind the user to export the file before closing it. My code looks like this:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Response As Long
Response = MsgBox("If changes have been made to this file without running the Monthly Claim Program, you MUST run the 'Export to Mastersheet' program. If you have not yet exported the file, please press NO, and do so. Otherwise, press YES to close this file", vbYesNo)
If Response = vbNo Then
Cancel = True
Else
End If
Call Remove_Macros_Menu
End Sub
However, no matter if I choose True or 开发者_运维问答False for the Cancel function, the file closes. Any ideas why this is?
Kathy,
Is the code in the ThisWorkbook module? It needs to be there, otherwise it won't do anything. Also, assuming the Remove_Macros_Menu deletes a custom menu for this workbook, I think you want the line that calls it inside the Else clause, especially if a button to export the code is in that menu. Otherwise you'll be deleting the menu even if they press no
Be aware of a flaw in deleting menus in the BeforeClose event, namely if the workbook is unsaved the user will see the "Save, Don't Save, Cancel" menu. If they choose Cancel the Close is cancelled, but your menu is still deleted.
精彩评论