BeforeSave Event not saving...

jfgoodhew1

New Member
Joined
Oct 16, 2012
Messages
19
Hi Guys,

I wrote a beforesave event in the Workbook module in Excel 2010 VBA, and the save fails. I've simplified it, commented out my entire block and written a lovely simple one (shown here), and it still fails. Any help much appreciated!

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'First checks state of boolean 
'Prevents save twice behaviour in VBA
'Boolean set to true on Workbook_Open event

Cancel = True

If ThisWorkbook.EnableBeforeSave = True Then
    ThisWorkbook.SaveAs "2014-09-08-INV.xlsm"
    MsgBox ThisWorkbook.Saved & " on save"
Else
    MsgBox "ThisWorkbook.EnableBeforeSave is " & ThisWorkbook.EnableBeforeSave
End If

If ThisWorkbook.Saved = False Then MsgBox "There was an error saving the spreadsheet."
    
End Sub

Notes:
Every time I click Save I get False as ThisWorkbook.Saved and "There was an error saving the spreadsheet."
Save As does the same, and I tried putting code into a BeforeClose event to save it - no joy.

Any clues why this might be? What have I screwed up?? ... Been staring at this for ages.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jfgoodhew1

New Member
Joined
Oct 16, 2012
Messages
19
OK So I realised what changed: the boolean test for save/not save. ***
Before that, I was using Application.EnableEvents - which I read was bad.
I wrapped only the save line in Application.EnableEvents = False followed by True after the save.
Presto, it works.

Very interested to know why it fails without though!

*** By the way, that boolean should have a line immediately above Else, and it's defined as Public EnableBeforeSave As Boolean in the ThisWorkbook module.
Code:
ThisWorkbook.EnableBeforeSave = False
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,893
Messages
5,834,274
Members
430,273
Latest member
Windrunner

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