by Allen Wyatt
(last updated June 8, 2019)
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:
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:
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 Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Excel allows you to protect your worksheet data in several different ways. Unfortunately, it doesn't allow you to protect ...Discover More
Excel provides built-in capabilities to protect your workbook files. If you apply these capabilities, it is possible that ...Discover More
When working with sensitive information, you always need to be concerned that your data doesn't fall into unwanted hands. ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.