Always Opening a Workbook that is Editable

by Allen Wyatt
(last updated August 21, 2017)


Patrick wonders how to stop Excel from opening workbooks in Protected view. He wants to 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 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 reprotect those worksheets. This approach assumes, of course, that there is not 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 tis 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, and 2013.

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. ...


Embedding TrueType Fonts

If you need to make sure that the fonts in your document can be used by another person or on a different system, you'll ...

Discover More

Adjusting Spell Check for Internet Addresses

When you check the spelling of worksheet data, you may want to spell checker to either ignore or check Internet addresses ...

Discover More

Slowing Down Mouse Selection

Ever tried to select a range of cells using the mouse, only to have the cells scroll by so quickly you can't make the ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros 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

Saving a Workbook Using Passwords

If you want to protect your workbook so that others cannot open or change the information it contains, an easy way to ...

Discover More

Limiting Who Can Delete Data

Excel allows you to protect your worksheet data in several different ways. Unfortunately, it doesn't allow you to protect ...

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}] 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 eight more than 6?

2014-01-04 13:06:31

Ashit Shroff

Can we keep this macro for future use?

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.