CODEWITHSUNDEEP
excelvba
Aurelius
Aurelius

Reputation: 485

Stop the Save As prompt when users make changes then close workbook

I have managed to supress the "Save as" method of going to File > Save as with the below:

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

    If SaveAsUI = True Then Cancel = True

End Sub

I have made the file read only, so the "Save" option stops them from saving this way.

However, if they make changes to the sheet the workbook they are prompted to Save their changes. Clicking "Yes" will bring up the "Save As" prompt.

My ultimate goal here is to stop them saving over this sheet or being able to save a copy of the sheet anywhere. I want them to be able to open the sheet, use its functions/features and that is it.

VBA is password protected.

They will access it via a shortcut, the file is not on their machine (I know a local copy is created, this is my next challenge) The file itself will be on a server controlled by Active Directory.

Upvotes: 1

Views: 1145

Answers (1)

Ralph
Ralph

Reputation: 9434

Since it seems that we agree that Excel does not provide any real protection, this is probably what you are looking for:

(1) Make sure that the user does not get a question about saving changes when the file is closed.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Close SaveChanges:=False

End Sub

(2) Making sure the user cannot print the Excel file:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Cancel = True

End Sub

(3) You can even automatically close the file when it is deactivated to try and make sure that they don't create a new Excel file in which they reference your "protected" file:

Private Sub Workbook_Deactivate()

ThisWorkbook.Close SaveChanges:=False

End Sub

Please note that you cannot / should not use all subs together as they are partically interdependent. So, if you are closing the file using option (1) with the event BeforeClose then the sub Deactivate will fail as there is no open Excel file to close anymore (and vice versa).

Upvotes: 2

Related Questions