How to Password Protect Excel Read Only

Some Excel files are the consequence of months of development and you may not want to pass this piece of work to everyone lightly. Exist it for protecting your intellectual holding, monetizing your data model, or only preventing user errors, in that location are several reasons why you might want to password protect Excel workbooks.

Excel offers various means to password protect your spreadsheets in the form of built-in features and VBA code. Although there really is no cookie-cutter approach to absolutely make sure a workbook is completely secure, the methods we're going to cover in this article are a good first. The biggest culprits in this half-hope are the availability of brute-force password decryption tools, and the way Excel keeps data in files. Let'south take a await at how you can protect your spreadsheets and limit certain features to your users.

"Very" Subconscious Worksheets

'very' hidden worksheet might look like a typo, but information technology'southward simply what Excel calls this method. First, permit's take a look at the "not-so-very" hide pick. You may already know that you tin can hide a worksheet by right-clicking the sheet name and clicking Hibernate.

Elementary, right? It's also just as easy to unhide a sheet. All you lot need to do is right click over again and select Unhide instead.

Then, what is with the "very" hidden? This is an alternative method to hibernate a worksheet, and worksheets hidden this way can't exist made visible once more by right-clicking and unhiding. Excel calls this worksheet land "very hidden" nether the constant name xlVeryHidden.

This belongings must be set using the Visual Basic for Applications (VBA) module of Excel. Past default, this feature is disabled and demand to be activated from the Option menu.  To open VBA window, press the Alt + F11 key combination. You will encounter a new window with canvass names and properties at the left-side panel. Click on a sheet name you want to hide (in the example below we selected Sheet2) and select 2 – xlSheetVeryHidden.

Once a canvass is made 'very hidden', the selection to unhide it will not be available in the sheet options menu when yous correct click.

Even if at that place is a 'normally' subconscious canvas, we don't meet our 'very hidden' canvas at the list.

This is a very useful feature for when you want to truly hide a worksheet, because the sheet volition not exist accessible to well-nigh Excel users. However, this method will only offering protection against those who are not familiar with the "very hidden" worksheet characteristic and VBA. At that place is another method that allows hiding worksheets in VBA window as well, only we will come up to that later on.

Protect Worksheet and Workbook

Using the Protect feature is a pretty straightforward way to password protect Excel files by creating read-merely spreadsheets. With this, you tin can prevent users from changing any prison cell, worksheet, or workbook elements by password protecting. The Protect feature can be found under the REVIEW tab in Changes section.

Protect Canvass menu gives you several options to configure the terminate-user feel. Here, y'all can fine-tune workbook access by selecting immune features from the list.

Protect Canvass characteristic locks merely cells that are marked as Locked. If your cells are not locked, they will allow editing by anyone. To help you lot with this procedure, Excel will identify warning icons on cells with formulas that are non locked.

Y'all check or lock/unlock cells from the Protection tab in the Format Cells window. The Format Cells window can be accessed by right-clicking a prison cell, and then clicking the Format Cells option.

In addition to the Protect Sheet feature, Protect Workbook window gives you two options, Structure and Windows. Past protecting the Structure of a workbook, yous tin forbid users from modifying sheet names, position, or visibility. This will too lock the power to add or delete any worksheets.

Countersign protecting will lock also "normal hidden sheets". The "very hidden sheets" and VBA methods become redundant at this point, considering all worksheet related options will be disabled.

The Windows selection is bachelor merely in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac. This pick prevents users from moving, resizing, closing, and hiding/unhiding workbook windows.

Y'all might be thinking that these measures will keep your workbook rubber. Notwithstanding, information technology's always safe to assume that Protect Sheet and Protect Workbook passwords can exist croaky. There are 2 known ways to practice it:

1. Brute Force Method
2. Modifying file XML

The Brute Force Method is the most straightforward mode of neat countersign protected Excel files. This essentially means trying every possible password combination until you get the right 1, very much like forcing a regular lock. A long and complex password with special characters volition make it longer to crack as the combinations that demand to be tested increment rapidly. However, information technology'south non a skillful idea to assume that your password is unbreakable.

Excel uses a hash algorithm to store passwords. The hash algorithm encrypts the password to make it not look like your bodily countersign when information technology'southward stored in your file. The reason behind this is to block users from reaching password databases or the XML file to find the actual password characters. The encrypted countersign can be decrypted only by using the aforementioned hash algorithm that was used to encrypt it.

The flaw in this arroyo is that different passwords may generate the same hash values. Essentially, Excel's hash algorithm isn't very sophisticated to let for better operation. The image below shows how a password is kept inside the workbook.

Note: Regardless of obvious threats, we recommend using a long circuitous password that contains special characters, because doing then volition certainly increment the time information technology takes for anyone to crack it.

Modifying XML method is where you really crack the password not try to predict information technology. Microsoft Part files have started using new file formats with additional letters at the end (i.e. *.xlsx, *xlsm, *.docx). These new file types work very much like compressed archive files. If yous oasis't already, try renaming an XLSX to alter its file extension to ZIP and so open it. You lot volition see folders and files just like in a regular compressed content folder.

Workbook.xml file contains the workbook data, while files under the worksheets folder contain the XML files. If you open an XML file with a word processor (i.e. Notepad) you volition come across the XML tags like from our encrypted password example earlier. You tin can even modify XML files directly from hither. If you delete the sheetProtection tag in the XML file, save the file somewhere else, and supervene upon with the original one, you've got yourself an unprotected file!

Modify/Read-Merely Password

You can salvage your file with a password to ensure that the file remains in read-only mode. The difference betwixt this arroyo and Protect method is that while Protect method allows you lot to lock specific cells or actions, Modify Password method prevents all kinds of updates (except for the Salvage As feature). To add together a password and only allow modifying the workbook,

1. Open the Save As
2. Click General Options under the Tools menu
3. Type in the password into the Password to change box
4. Click OK and so re-enter your countersign
5. Save

Check File Location

Even if you lot have a modify password set up up, other users can still open your file in read-only mode and salve it somewhere else. Although this method requires a fleck of VBA knowledge, you can use the code below to prevent users from copying your workbook.

Sub LocationCheck ()      Dim MyPath As String      MyPath = "C:\User\Desktop\Workbook Security"      If Application.ThisWorkbook.Path = MyPath So          UnProtectMe      Else          MsgBox "File location is incorrect."      End If  End Sub

This code compares the current location of the file with where it'south supposed to exist. If the location is correct, it will then run a code under the name of ProtectMe, otherwise information technology will send a message to the user saying that the file is in the wrong directory.

If y'all place the LocationCheck sub routine nether Workbook_Open or Workbook_BeforeSave events, you tin password protect Excel on the 'file open step' and before relieve. Workbook_Open or Workbook_BeforeSave events tin can exist created under this code (ThisWorkbook).

When using the VBA methods, all options mentioned under the Protect Worksheet and Workbook section can be applied here. All of these options are only reliable if other users do not know how to use VBA. To take it one step farther allow's take a look at how to password protect Excel via VBA code and properties. In VBA window,

1. Click Tools
2. Click VBAProject Backdrop (VBAProject is a default proper name for a project, it can be edited in the same window)
3. Become to the Protection tab
4. Check Lock project for viewing
5. Enter your password into the Password field
6. Click OK to save

Changes volition take event when you shut and then re-open the workbook, y'all volition be prompted with a countersign screen.

Every bit we mentioned in a previous department, VBA codes and passwords are kept in a compressed file format. The deviation is that they are not stored in XML files simply inside a BIN file (i.e. vbaProject.bin). BIN files are encoded in the HEX system and need a special HEX editor to read and edit.

Our encrypted countersign come after DPB=. If someone changes DPB to DPx and update the BIN file in the compressed archive, Excel will give an error saying that file contains an invalid key.

Click Aye, relieve the file and re-open up it. The VBA code will exist available.

Open Password

All other methods we've covered in this article are for limiting access of some other user. These methods are expert for the situations where we desire users to see our file, merely non copy or alter it. If you lot want preclude all access to your file (including seeing its contents) y'all tin employ the Open Password characteristic to countersign protect Excel files, which prevents opening the file without a password. Excel encrypts the ZIP annal as well, therefore users cannot modify the XML or BIN files to crack the password. Although this is ane of the best methods available to protect files, please go along in mind that this method is all the same vulnerable to animate being force hacking.

Follow the same steps from the Modify/Read-Merely Password method, but this time, enter a countersign into Password to open: textbox.

1. Open Salvage As
2. Click on General Options under Tools
3. Fill the Password to open box with password
4. Click OK and re-enter your password
5. Save

When you re-open up the workbook, yous will get a password prompt and no other options.

pennslitione.blogspot.com

Source: https://www.spreadsheetweb.com/password-protect-excel-files/

Share this post