Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views :
    Excel formulas and tutorials

    Excel formulas and useful tools

    Home / VBA / Excel file unsaveable

    Excel file unsaveable

    /
    /
    /
    369 Views

    Some companies use Excel files through intranet networks for all their staff to have access to certain information and automated reports. This usually means that this kind of file should be unsavable by the staff in order to preserve a certain degree of accuracy and format.

    Unsaveable Excel File

    Setting Excel file as unsavable

    Without altering the menu of the application to take out the Save option, a file can be protected from accidental saving by using VBA code inside to prevent executing the Save or Save As commands. All you need to do is insert the following VBA code into the ThisWorkbook section in Project Explorer:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, CANCEL As Boolean)
        
        A = MsgBox(“This File cannot be saved !!!”, vbOKOnly)
        Application.DisplayAlerts = False
        Application.ActiveWorkbook.Close False

    End Sub

    This code works by alerting the user who wants to Save the file telling him that this is an unsaveable type of Excel file and then close the file to protect it.

    Limitation of the unsaveable solution

    Making Excel file unsaveable by this method does not guarantee the success as the VBA code can be paused from execution using the Ctrl+Pause (Break) and in pause mode all the Excel applications functions work including Save.

    A more viable solution is to set the file as ReadOnly on the server and in this way no one can update it.

    Enjoy!

    Please leave a comment bellow, so I can understand if the article was useful or I have to update it or offer more details.

    Save

    Save

     
    • Facebook
    • Twitter
    • Google+
    • Linkedin
    • Pinterest

    4 Comments

    1. are your sure this code is correct?

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

      A = MsgBox(“This File cannot be saved !!!”, vbOKOnly)
      Application.DisplayAlerts = False
      Application.ActiveWorkbook.Close False
      End Sub

       

    Leave a Comment

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    This div height required for enabling the sticky sidebar