Code to prevent saving preventing me from saving code to prevent saving - LOL

[ktielens]

I am using the following code to prevent users from saving a document. The problem is, I can't save the code in the document either as it's preventing all saving. OMG!!! How funny.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Please don't use the save button"
Cancel = True
End Sub

Please help.

 

[Jonmo1]

Try opening the book with Macros Disabled.
Hold Shift Key down when you double click the file to open it.

 

[ktielens]

Try opening the book with Macros Disabled.
Hold Shift Key down when you double click the file to open it.

That doesn't seem to work. I open it the way you mentioned, add the code, and when I try to save it it just runs the code and prevents me from saving.

 

[mole999]

at the top of the module to prevent saving add exit sub so it can't trigger

 

[Jonmo1]

That doesn't seem to work. I open it the way you mentioned, add the code, and when I try to save it it just runs the code and prevents me from saving.

I guess that method of temporarily disabling macros doesn't work on everyones PC.
The idea is just to disable macros, if you have to do that through Excel's options, then that's what you do.

You could also disable events
In the VBA window, Click View - Immediate Window
IN there type
Application.EnableEvents = False

Now you should be able to save.

I don't think you'd have to turn it back on after you save/close/reopen, but you might
Just repeat the procedure and change False to True.

 

[ktielens]

at the top of the module to prevent saving add exit sub so it can't trigger

I tried this:

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

'prevents users from saving and shows message box when they try
MsgBox "Please do not use the save button"
Cancel = True

End Sub

Now it just doesn't work at all. Where exactly does the Exit sub go? I think that is my problem.

 

[Jonmo1]

How did you ever save the book with that code in it to begin with??

Since it specifically runs on Save, it should NOT run on Save AS
Try using Save AS, but then use the same filename and location and say yes if you want to replace the existing.

 

[ktielens]

How did you ever save the book with that code in it to begin with??

Since it specifically runs on Save, it should NOT run on Save AS
Try using Save AS, but then use the same filename and location and say yes if you want to replace the existing.

It's not saved with that code. I just copied it out of the VBA to show it in the thread.

 

[Jonmo1]

Oh, I see.
I thought you already had that code in a book, and were now trying to add some other code but couldn't because it was preventing save.
I get it now.

I thought there was a simpler way, but you can put a keyword in some designated cell
And add an If to test that cell for a specific value
If that specific value is in the cell, proceed with the save, if Not, then cancel the save.

I used A1 on Sheet1 - adjust as needed.
If you put Go in that cell (Case sensitive) it will allow you to save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("Sheet1").Range("A1").Value = "Go" Then
    Sheets("Sheet1").Range("A1").ClearContents
    Exit Sub
End If
MsgBox "Please don't use the save button"
Cancel = True
End Sub

 

[ktielens]

I guess that method of temporarily disabling macros doesn't work on everyones PC.
The idea is just to disable macros, if you have to do that through Excel's options, then that's what you do.

You could also disable events
In the VBA window, Click View - Immediate Window
IN there type
Application.EnableEvents = False

Now you should be able to save.

I don't think you'd have to turn it back on after you save/close/reopen, but you might
Just repeat the procedure and change False to True.

Got it. Disabling the events was necessary. Thank you so much.