asked on

Making a multi-sheet excel file read-only and not editable in any way, nor be saved as another document

We have dozens of Excel workbooks that need to be shared with an external group for litigation purposes. We are trying to find a way to make the Excel files uneditable, or Read Only. We do not want them printing altered versions or saving another version they could edit.

I have tried using the advanced save as read only option, but that still allows the user to write in and edit cells and then just save as another name.

I know one way to lock down a sheet, by formatting all the cells as locked and then restricting the document in the view tab, but this requires us to go in to every sheet and lock all cells and lock each sheet separately. There are about 20 sheets in each of these workbooks, so repeating this process x the dozens of workbooks would be far too time consuming. Is there a better way?

We are using Excel Office 365 ProPlus.

Edit: Why we can't just PDF:
"when these documents were created, none but the summary sheets were ever meant to be printed, and so the print area is all messed up in all of them, so you get tons of blank pages or pages with the data cut up in very awkward ways. Trying to go back and fix all of the print areas now would be even more time consuming."

Roy Cox

Why not save them as pdf?

c_may

ASKER

That was the first thought, but when these documents were created, none but the summary sheets were ever meant to be printed, and so the print area is all messed up in all of them, so you get tons of blank pages or pages with the data cut up in very awkward ways. Trying to go back and fix all of the print areas now would be even more time consuming.

Fabrice Lambert

Locking all sheets / cells on all workbooks can be done via script.
But the files will still be copyable.

Roy Cox

Excel is not really secure most passwords can easily be removed with the right knowledge. That's why I suggested pdf.

You could possibly loop through the workbooks and set the PrintArea depending on the layout of the sheets, but it would probably take some time

c_may

ASKER

I understand the passwords could be cracked, I am tasked with just creating a reasonable level of protection for these documents so a casual user can't edit them by simply saving as.

What would a script look like to lock all sheets/cells on all workbooks? And could such a script be combined along password protection right after?

Martin Liss

I haven't used them but there are 3rd-party applications like this one that purport to being able to secure Excel workbooks.

Martin Liss

If you want to prevent printing, set up print areas on your worksheets that don't include any of the data.

Rob Henson

I vaguely recall there is a setting in OneDrive whereby you can share a file (or a folder) and set the file/folder to Read Only for the person with whom it is shared with an additional setting whereby the file can't be copied using Save As either.

Or is that just in my head???

c_may

ASKER

@Martin: We wouldn't care if they printed it if the cells were uneditable (as they would be in a protected document and with cells locked), but I'll remember that. That addon looks promising I will look into it.

@Rob: If that is possible that would be great.

Roy Cox

Assuming all workbooks are in the same folder and this code is also in a workbook within that folder. Test on some dummy files

Option Explicit

Sub protectAll()
    Const sPw As String = "Secret"
    Dim sPath As String
    Dim sFileName As String
    Dim oWb As Workbook
    Dim oWs As Worksheet



    On Error GoTo Error_Exit


    With Application
        .EnableEvents = False
        .ScreenUpdating = False

        sPath = ThisWorkbook.Path
        sFileName = Dir(sPath & "\*.xl*", vbNormal)
        Do Until sFileName = ""
            If sFileName <> ThisWorkbook.Name Then
                Set oWb = Workbooks.Open(sFileName)
                For Each oWs In oWb.Worksheets
                    oWs.Protect sPw
                Next oWs
                oWb.Protect sPw
                oWb.Close True
            End If
            sFileName = Dir()
        Loop

        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Set oWb = Nothing



    On Error GoTo 0
    Exit Sub

Error_Exit:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") whilst protecting files from " & sPath



End Sub

Open in new window

Roy Cox

The problem with such software is that it creates exe files which are sometimes difficult to distribute..

Here's a free app to convert to exe files

Martin Liss

...if the cells were uneditable...

I believe that in native Excel, any sufficiently motivated person can find ways around Excel's security features.

Arana (G.P.)

By default all cells are already locked, you will still have to protect each sheet in every file.

Regarding passwords from some 2 or 3 version ago excel password protection is really secure and not easily crackable with any tool,

- Lets you configure the CNG cipher algorithm that is used. The default is AES.
- Lets you configure the number of bits to use when you create the cipher key. The default is 256 bits.

If you use easy and short passwords then brute forcing will be easy no matter the algorithm or number of bits, use long phrases with numbers etc. and don't forget them.

in office 365 you also have IRM Information rights Management, this kind of protection is made by creating a set of rules and appying to the document, this so far is not crackable and you can even restrict printing, editing, saving or forwarding.
GO HERE MS IRM

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

c_may

ASKER

Information Rights Management was the solution that worked for us. Thanks for everyone for your help.

Rob Henson

Glad that worked for you.

Arana (G.P.)

Glad it worked for you, this was one of the 3 options I gave that we use most and like better.