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:
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:
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
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:
Code:
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:
Code:
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