Always Opening a Workbook that is Editable

Patrick wonders how to stop Excel from opening workbooks in Protected view. He wants the file to open in Normal view such that anyone can open it, read it, and edit it.

The answer depends entirely on what is meant by "Protected view." If you mean that you want the worksheets to be unprotected, that is relatively easy to do via a macro. If you are really talking about what Excel terms "Protected View," then that is an entirely different situation.

If you want to make sure that the workbook is unprotected so it can be edited once opened, then a macro as simple as this may do the trick:

Private Sub Workbook_Open()
    ThisWorkbook.Unprotect
End Sub

Of course, this doesn't unprotect individual worksheets. That involves a few more steps since you need to cycle through each of the worksheets in the workbook. The following macros should be placed in the ThisWorkbook module for the workbook whose sheets you want unprotected:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Reprotect sheets that were protected on open workbook
    Call ReprotectSheets
' Save changes in ThisWorkbook
    ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
' Unprotect all sheets in this workbook
    Call UnprotectAllSheets
End Sub

Note that the macros do little more than call other macros that do the real work. These other macros can be placed in a standard module in the workbook:

Public arrProtectedSheets()

Sub ReprotectSheets()
' Reprotect worksheets that were protected on WorkbookOpen
    Dim i As Integer

    For i = LBound(arrProtectedSheets) To UBound(arrProtectedSheets)
        ThisWorkbook.Worksheets(arrProtectedSheets(i)).Protect
    Next i
End Sub

Sub UnprotectAllSheets()
' Unprotect all worksheets in this workbook
    Dim wks As Object
    Dim i As Integer

    i = 0
    For Each wks In ThisWorkbook.Sheets
        If IsSheetProtected(wks) Then
            ' Remember names of protected sheets in order
            ' to reprotect them on WorkbookClose
            ReDim Preserve arrProtectedSheets(i)
            arrProtectedSheets(i) = wks.Name
            i = i + 1
            ' Unprotect sheet
            wks.Unprotect
        End If
    Next wks
End Sub

Private Function IsSheetProtected(ByRef wks As Excel.Worksheet) As Boolean
' Function returns TRUE If Worksheetsheet Is Protected
    With wks
        IsSheetProtected = (.ProtectContents Or _
          .ProtectScenarios Or .ProtectDrawingObjects)
    End With
End Function

The idea behind the UnprotectAllSheets macro is that it steps through each of the worksheets in the workbook and, if it is protected (determined in the IsSheetProtected macro), the name of the worksheet is stored in an array. The contents of the array are then used in the ReprotectSheets macro to once again re-protect those worksheets. This approach assumes, of course, that there is no password associated with any of the protected worksheets.

If you are actually wanting to make sure that the real "Protected View" is turned off, that is a different story. The settings for Protected View are controlled on a system-by-system basis in the Trust Center. You can see the settings by following these steps:

1. Display the Developer tab of the ribbon.
2. Click Macro Security in the Code group. Excel displays the Trust Center dialog box.
3. At the left side of the dialog box click Protected View. (See Figure 1.)



Figure 1. The Trust Center dialog box.

The actual Protection View settings available depend on the version of Excel you are using. As mentioned, these settings are controlled at a system level; they are not handled on a workbook-by-workbook basis. The upshot is that they cannot be circumvented by macro code. If they could, then it would render a system completely vulnerable to whatever code was in a workbook being opened—we would be back to the days of macro viruses that were common with some earlier versions of Office products.

Additional information on how the Protected View settings operate can be found on this Microsoft web site:

https://support.office.com/en-us/article/what-is-protected-view-d6f09ac7-e6b9-4495-8e43-2bbcdbcb6653?ocmsassetID=HA010355931&CorrelationId=fce5243e-6c41-4865-89e9-530f125ea252&ui=en-US&rs=en-US&ad=US

Whether a workbook opens in Protected View or not depends on a user's system settings. This means that control of what opens is entirely up to the individual user and cannot be "forced" by a workbook author. There are ways, though, that the impact of this can be mitigated, and they have to do with trust. Note that at the left of the Trust Center dialog box there are three settings that control this relative to workbooks:

• Trusted Publishers. Workbooks from whatever publishers are listed in this area are trusted and will open directly, bypassing Protected View.
• Trusted Locations. Workbooks stored in the locations listed in this area are trusted and will open directly, bypassing Protected View.
• Trusted Documents. Individual workbooks noted in this area are trusted and will open directly, bypassing Protected View.

For your workbook, then, to bypass Protected View, you need to be a trusted publisher, the workbook needs to be stored in a trusted location, or it needs to be noted on the system as a trusted document. All of these settings are, again, under control of the user and cannot be modified through macro code.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12873) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.