Prevent a MsgBox if its already been asked ?

[Pauljj]

I have 2 codes one which is beforesave and another beforeclose

Part of the beforesave code is

Private Sub WORKBOOK_BEFORESAVE(ByVal SAVEASUI As Boolean, Cancel As Boolean)

msg = MsgBox("Is this first week of the month, clicking YES will update Leave allowance links", vbYesNo, "Saving Incite")

If msg = vbYes Then ............

But I also have a beforeclose

This code hides some sheets, so therefore has a me.save command....


This of course then brings up the VB message above when it's not required, can anyone think of a bright idea that would stop it ?

Thank you in advance

 

[Jaafar Tribak]

Couldn't you also put a value in a cell in a hidden sheet or out of the way place. Then check that value @ msgbox time and if value = true, then show message box, otherwise don't show it.

Unless I am missing something, this is going to be a bit tricky to solve. You can do the following but it will stop working if the user clicks the No or Cancel buttons when prompted to save the changes from within the Before_Close event.

Private bClosing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    bClosing = True
    'rest of your code.....

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
    If Not bClosing Then
        msg = MsgBox("Is this first week of the month...")
        'rest of your code....
    End If
End Sub

The only foolproof work around to that particular scenario (where the user dismisses the saving prompt) is catching the user action and reset the bClosing flag.