Disabling Save Prompt in Excel 2

[BostjanPerdan]

Hi!

I would like to prevent the user from saving any changes he or she has made to the Excel workbook. How do I disable the Save Prompt that appears upon closing the workbook?

This procedure doesn’t work for me:

Private Sub Workbook_Close()
ActiveWorkbook.Saved = True
End Sub

Cheers,
Bostjan

 

[xlbo]

use the BEFORE_close event

 

[rmikesmith]

Bostjan,

You also might have a look at the Workbook_BeforeSave event. Your code can set a parameter to True effectively canceling the save operation.


Regards,
Mike

 

[BostjanPerdan]

xlbo,

I tried to use the Before_Close() event, but the code wouldn't even compile ... I would appreciate, if someone could give me a working example with this one.

Cheers,
Bostjan

 

[BostjanPerdan]

Unfortunately, neither solution works. ;-(

I get the following message in both cases: “Compile error: Procedure declaration does not match description of event or procedure having the same name.”

Could someone please give me a working example either with Before_Save or Before_Close event.

Cheers,
Bostjan

 

[PHV]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Saved = True
Cancel = True
SaveAsUI = False
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

To add to PHV's syntax of the event procedure: These must be located in the ThisWorkbook module, not a standard code module. You can type them in by hand (with exactly the correct syntax) or let Excel create the event procedure shell for you by selecting Workbook in the left dropdown of the code editor then selecting the specific event in the right-hand dropdown. If you do this for the BeforeSave event, Excel will create the following:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Hope this helps clarify things,
Mike

 

[BostjanPerdan]

It works! Thank you both ... this forum is great!

Cheers,
Bostjan

 

[BostjanPerdan]

The code works even too good and won't let me save itself ... is there a way around this?

What I would like to do, is just disable the save prompt and still be able to save the changes.

I tried to use the Before_Close event but it didn't do the trick for me?

Cheers,
Bostjan

 

[rmikesmith]

Bostjan,

Since we've come at this from a couple of angles and have gone through several iterations, please post all of the code you are now using related to the save issue. Also, could you clarify what you want to happen when the workbook is closed; e.g. User closes workbook --> workbook saves automatically without prompt; --or-- User closes workbook --> workbook closes without prompt or save.

The code works even too good and won't let me save itself ... is there a way around this?

Do you only need to save as the developer? This would be a different situation than the examples above.

Regards,
Mike