Application.DisplayAlerts

[doofusboy]

Having trouble with Application.DisplayAlerts = False. When I put a break on that line of code, then hit F8 key to execute it and then type ? Application.DisplayAlerts in Immediate Window, it tells me Application.DisplayAlerts = True ! [Application.EnableEvents also = True, if that matters]

Any idea how I can get alerts to shut off?

 

[Andrew Poulsom]

What alert are you trying to suppress exactly?

 

[doofusboy]

Have a user that wants several things to happen on file save/close. Am able to get that stuff coded in Workbook_BeforeSave and Workbook_BeforeClose, but am getting system messages "Do you want to save the changes you made to....?"

Trying to avoid that pop-up as I present user with form to exit without save or exit with save and don't want them to have to say yes or no a second time.

 

[Andrew Poulsom]

DisplayAlerts should work, but maybe this will help you:

http://spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/

 

[doofusboy]

Andrew, thanks for your responses and perhaps I can use Me.Save = True or Me.Save = False to keep the "Do you want to..." pop-up from appearing.

However, I've thus far had no success on WHERE to put this. Could you make a suggestion that can stifle the pop-up with this code?

Option Explicit
Public boolProject As Boolean
Public boolOK_Clicked As Boolean
Public strSaveType As String
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Call Workbook_BeforeSave(True, False)
    If boolProject = False Then Cancel = True
 
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
    On Error GoTo ErrorHandler
    frmOnSave.Show
 
    If ThisWorkbook.strSaveType = "NoSave" Then
        boolProject = True
        Cancel = True
        Exit Sub
    ElseIf ThisWorkbook.strSaveType = "Edit" Then
        boolProject = True
    ElseIf ThisWorkbook.strSaveType = "Final" Then
        If FinalSaveChecks = False Then
            boolProject = False
            Cancel = True
            Exit Sub
        Else
            Call modOctetPlateFileCreation.CreateSTDsList
            boolProject = True
            Cancel = False
        End If
    ElseIf ThisWorkbook.strSaveType = "" Then
        boolProject = False
        Cancel = True
        Exit Sub
    End If
 
    Exit Sub
 
ErrorHandler:
        MsgBox "Error occurred in " & ActiveWorkbook.Name & ": Workbook_BeforeSave." & vbCrLf _
        & vbCrLf & "Error #" & Err.Number & " - " & Err.Description
 
End Sub

 

[doofusboy]

Actually........based on your lead...........I was able to get the following to do what I need it to do.

Thanks for your help Andrew.

Option Explicit
Public boolProject As Boolean
Public boolOK_Clicked As Boolean
Public boolSaving As Boolean
Public strSaveType As String
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Workbook_BeforeSave(True, False)
    If ThisWorkbook.strSaveType = "Edit" Or ThisWorkbook.strSaveType = "Final" Then
        Me.Save
    End If
    If boolProject = False Then Cancel = True
    ThisWorkbook.boolSaving = False
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error GoTo ErrorHandler
    If ThisWorkbook.boolSaving Then
        Exit Sub
    Else
        frmOnSave.Show
        If ThisWorkbook.strSaveType = "NoSave" Then
            boolProject = True
            Cancel = True
            Me.Saved = True
            Exit Sub
        ElseIf ThisWorkbook.strSaveType = "Edit" Then
            boolProject = True
            Application.DisplayAlerts = False
        ElseIf ThisWorkbook.strSaveType = "Final" Then
            If FinalSaveChecks = False Then
                boolProject = False
                Cancel = True
                Exit Sub
            Else
                Call modOctetPlateFileCreation.CreateSTDsList
                If boolProject = True Then
                    Cancel = False
                ElseIf boolProject = False Then
                    Cancel = True
                End If
            End If
        ElseIf ThisWorkbook.strSaveType = "" Then
            boolProject = False
            Cancel = True
            Exit Sub
        End If
        ThisWorkbook.boolSaving = True
    End If
    Exit Sub
ErrorHandler:
        MsgBox "Error occurred in " & ActiveWorkbook.Name & ": Workbook_BeforeSave." & vbCrLf _
        & vbCrLf & "Error #" & Err.Number & " - " & Err.Description
End Sub