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

RusselJ

Board Regular
Joined
Aug 5, 2013
Messages
155
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:

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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
have you tried:
Code:
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

Board Regular
Joined
Aug 5, 2013
Messages
155
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
Have you tried putting Application.EnableEvents = False in SaveFile, so the Before_Save isn't called again?
 

RusselJ

Board Regular
Joined
Aug 5, 2013
Messages
155
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,467
Messages
5,624,913
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top