VBA: File Saving to be disabled

  • I have an excel file containing some confidential information. I want to place this on the server so that users may view the contents but i do not want the user to be able to save the file on his PC.

    I tried the following;
    Private Sub Workbook_BeforeSave _
    (ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then Cancel = True
    End Sub

    It works fine if the users security is set to medium for macros. However if it is set to high, macros are disabled and the file can be saved with another name.

    Is there another way to do this?

  • Savior,

    As a VBA solution what you have is as good as it gets.
    My only reccomendation would be that you do the following:

    1. Using the VBA editor set the Visible property of all worksheets (bar the one mentioned below) to 2-Very Hidden.

    2. Create a macro that goes in the Workbook_Open event that shows all the relevant worksheets bar a 'Splash Sheet' that tells the user he must have macros enabled to view the sheets.

    3. Create a macro in the Workbook_BeforeClose event that hides them all again.

    4. Password protect the VBA project.

    NB: This will only stop the honest/less computer literate people from hacking your work.
    If a users PC has high security (Macros never to run) set and this cant be changed to Medium (prompt for macros) then all was in vain.


  • Just a side comment.

    Per your first post, it sounds like the user can only save the workbook with a different name. Hence your original is still intact. If you are worried about data transfer, then remember that if you let folks look at the workbook they can also do a copy and paste of the data they want to another workbook. This method gives them whatever they want to have without needing to save the workbook.

  • An add on to Tom's comment.

    If you are using XP (not sure about earlier versions) - -
    Lock all the cells on the worksheet (under Format / Cells / Protection)
    Turn Protection on (Tools / Protection / Protect Sheet)
    Assign a protect password
    In the "Allow All Users of this worksheet to:" box - - make sure nothing is checked (especially, select locked cells and select unlocked cells)

    Then user can scroll around the sheet using the scroll bars, but can't select any cells - which locks them out of copy and paste function.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!