VBA: Second prompt to save when calling custom script BeforeSave. Suggestions?

[RusselJ]

Hi All

I am trying to call a custom save script only if one cell doesn't = Yes.

This code is in this thisworkbook module which calls SaveFile:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.Sheets("Claim").Range("Saved").Value <> "Yes" Then
Cancel = True
Call SaveFile
End If
End Sub

This will run the below code which will suggest that the file is saved with a specific filename in a specific folder and will toggle the Yes so that the script is not called again:

Sub SaveFile()
Dim FolderName As String
FolderName = ActiveWorkbook.Sheets("Claim").Range("Foldername").Value & "\"
Filename = "Apportionment " & ActiveWorkbook.Sheets("Claim").Range("ShortRef").Value
 
 With Application.FileDialog(msoFileDialogSaveAs)
 .AllowMultiSelect = False
 .InitialFileName = "C:\Cases\" & FolderName & Filename 
 
 If .Show <> 0 Then
 ActiveWorkbook.Sheets("Claim").Range("Saved").Value = "Yes"
ActiveWorkbook.Saved = True
 .Execute
End If
End With
End Sub

That all works fine apart from when the user attempts to close the workbook. It will run the SaveFile macro to prompt to save the file fine but once it has finished it will display another prompt asking whether the user wants to save the file.

I'm not sure how to overcome this. I thought including ActiveWorkbook.Saved = True would fix it.

Do I need something in the Workbook_BeforeClose to prevent the second prompt?

Really scratching my head over this one and would appreciate any suggestions

Russel

 

[sijpie]

have you tried:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If ActiveWorkbook.Sheets("Claim").Range("Saved").Value <> "Yes" Then
        Cancel = True
        if ActiveWorkbook.Saved = false then
            Call SaveFile
        end if
    End If
End Sub

 

[RusselJ]

Hi Sijpie

Thanks for your suggestions. When the user goes to close the workbook it still prompts the user to save twice. Once before the the SaveFile macro is ran which then displays the saveas dialog as it is supposed to and this does save the file, but then a second prompt asking whether to save appears immediately after, just before the workbook closes.

I am trying to tell Excel that the workbook is saved after the SaveFile macro is run.

There must be something I am missing here?! I would appreciate any other suggestions.

Thanks again

Russel

 

[mikerickson]

Have you tried putting Application.EnableEvents = False in SaveFile, so the Before_Save isn't called again?

 

[RusselJ]

Hi Mike

Yes that does work but then events stay disabled unless they are re enabled. I could put EnableEvents = True in the before close event but the SaveFile macro runs if the user selects save and not just when the user is closing the workbook so events would stay disabled until the workbook is closed which isn't ideal. Unless you can think of another way?

 

[mikerickson]

Reenable Application.EnableEvents as the last line in SaveFile.