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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

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