Always Opening a Workbook that is Editable

Written by Allen Wyatt (last updated June 8, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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()
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
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)
    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
        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.)
  4. 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:

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.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Understanding Number Formatting Codes

When creating custom formats, you can employ a wide range of codes to define your formatting pattern. This tip focuses on ...

Discover More

Turning Off Sharing

All good things must come to an end at some point. When you are done sharing your workbook with others, this is how you ...

Discover More

Quickly Displaying the Tabs Dialog Box

Setting tabs in a paragraph is a common task. This is most easily done by using the Tabs dialog box. Displaying the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Protecting a Workbook from Opening in Other Programs

When working with sensitive information, you always need to be concerned that your data doesn't fall into unwanted hands. ...

Discover More

Removing Protection from a Protected Workbook

Excel provides built-in capabilities to protect your workbook files. If you apply these capabilities, it is possible that ...

Discover More

Cannot Add Worksheets to a Workbook

If you cannot add a worksheet to your workbook, it could be because the workbook is protected. This tip shows how to ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 7?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.