Always Opening a Workbook that is Editable

by Allen Wyatt
(last updated August 21, 2017)

1

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

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx

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.

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

MORE FROM ALLEN

Sheets for Days

Need a quick way to have a worksheet for each day in a month? Here's a macro that makes the worksheet creation a snap.

Discover More

Comparing Document Versions

Do you need to compare two versions of a document to each other? Word provides a tool that can make this easy, as described ...

Discover More

Putting an X in a Clicked Cell

Need to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Protecting an Entire Folder of Workbooks

Want to protect the Excel information stored in a particular folder on your system? There are a number of ways you can ...

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

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 6 + 0?

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
Subscribe

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.