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

 

[Gary McMaster]

Maybe this?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Static bSaved As Boolean

If bSaved Then Exit Sub

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

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

    bSaved = True

End Sub

Gary

 

[jasonb75]

Similar to Gary's suggestion, if I remember correctly, Static only holds the variable within the current procedure, it has to be declared at the head of the module to be open to all procedures in the module.

Public bSaved As Boolean
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
msg = MsgBox("Is this first week of the month...
 
If msg = vbYes Then 
    bSaved = True
    rest of vbYes procedure
End if
 
End Sub
Private Sub Workbook_BeforeClose(....
 
If Not bSaved Then 
    msg = MsgBox ....
 
Else
 
    .....
 
End If
End Sub

 

[Pauljj]

I tried that Gary but it still came up ?

 

[Pauljj]

Jason that didn't work either, the thing is, when closing the workbook the message shouldn't come up

 

[jasonb75]

It appears that you can't pass variables between events, but you can pass objects, which can be used to an advantage.

This method works, just need to edit your code into it.

Dim bSaved As Object
Sub Workbook_BeforeClose(Cancel As Boolean)
    If bSaved Is Nothing Then
        ' not saved
    Else
        ' already saved
    End If
End Sub
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If bSaved Is Nothing Then
        ' not saved
        msg = MsgBox("is this the first...", vbYesNo)
        If msg = vbYes Then
            Set bSaved = ThisWorkbook
            ' do some other stuff
        Else
            ' do this if user answered no
        End If
    Else
        ' already saved, do this instead
    End If
End Sub

 

[Pauljj]

Thanks Jason, I am off now for the day but will give a go in the morning, thanks once again
Paul

 

[Pauljj]

Sorry but this doesn't work either. When I close the workbook, it will need to save every time, as part of the closing process involves hiding sheets on closure. So it will always need to save. What I am aiming for though is, if it saves on closure the VB message doesn't come up....bearing in mind I have a VB message in the beforesave code

It might be that I just can't have what I am looking for ?!?!?!

 

[jasonb75]

It does work Paul, that was an example format of how to do it, though it sounds as though your event procedures may not be set up as I thought.

If everything in the before close event should happen regardless then you don't need anything for this test on there, in the before save event your basic format should be.

Dim bSaved As Object
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If bSaved Is Nothing Then
       
 ' everything to do if not saved, including the message box
        
    Else
        ' anything to to if already saved
    
    End If
 ' anything to do each time workbook is saved 
    Set bSaved = ThisWorkbook
End Sub

Sections in red text are optional if you need them, if not, leave them out.

The only other way is to write a value to a cell on a hidden sheet, and case your code based on that value.

 

[sous2817]

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.