Prevent saving workbook

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
So I thought if I placed this code into the Workbook Module it would prevent the workbook from being saved or saved as. What did I miss?


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Application.DisplayAlerts = True

End Sub
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You need to stop the saving of the file
Code:
[COLOR=#141414][FONT="open sans"]Cancel = True[/FONT][/COLOR]
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
Thank you, yes I tried that but I could not save the wb with the code in it....



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub<strike></strike>
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
You have not actually cancelled the save in your code

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

Before you become frustrated, remember to disable macros BEFORE trying to save the file with the second procedure above
I am sure you can work out why ;)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

yes I tried that but I could not save the wb with the code in it....
Chicken and the egg scenario!!

Close the file
To disable macros hold the SHIFT key down before double clicking on the file name when opening the file
Amend the code
Save the file
 
Last edited:

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
Did not know about the Shift open. Tried that and it did stop running macros but I still could not save the code. Do I need to Shift while saving too?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is what I do, and it works for me ...

Hold {SHIFT} as the workbook is opened
Amend the macro
Save as normal
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
That is what I did but it does not save. Tried holding the shift UNTIL the book was fully open too and still did not work. Any ideas?
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
So I tried it with only one statement and it worked. This code is the issue, do I need it?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Maybe not - test it

Specifically what happens after you amend something in the workbook and then try to close the workbook (using X in right corner) ?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,734
Messages
5,597,809
Members
414,177
Latest member
RezaAkhund

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