How to Protect Excel Workbook from Opening and Editing

Part 1:How to protect Excel workbook from opening

Add a Document Open Password for an Excel workbook so that all the users should type the correct password to open the workbook.

Step 1: In the opened Excel workbook, click on the File and select the Info. Click on the Protect Workbook button and select the Encrypt with Password from the expanded options.

Step 2: Type your password in the “Encrypt Document” dialog and click OK. Type the password again in the “Confirm Password” dialog and click OK to confirm.

Then the Excel Workbook has been protected from opening. A password will be required to open this workbook.

Part 2: How to protect Excel workbook from editing

You can mark an Excel workbook as Final or add password to protect the current sheet from being changed.

Way 1: Mark as Final

Step 1: Similarly click on the Protect Workbook button. Then select Mark as Final.

Step 2: Click on OK to confirm.

Now this Excel workbook has been marked as final to discourage editing. The users will be unable to edit in the cell. But there will be one yellow warning under the Excel tool bar. As long as the user clicks on the Edit Anyway, he can edit the Excel workbook. But the user had better not do this without the author’s permission if you are not the author.

Way 2: Protect current sheet

Step 1: Click on the Protect Workbook button and select Protect Current Sheet.

Step 2: In the Protect Sheet dialog, type the password to protect the worksheet. Under the Allow all users of this worksheet to, you can select the options to decide what types of changes the users can make to the worksheet.

Step 3: Type the password again to confirm.

Now the Excel workbook has been protected from editing. Once you attempt to modify the cell, it will prompt the warning message to tell the cell is protected and read-only. Only removing the password from the protected Excel workbook, can you modify the cell.

Another way to protect Excel workbook from opening and editing

Step 1: In an opened Excel workbook, click on File -> Save As.

Step 2: In the Save As dialog, click on the Tools button and select General Options.

Step 3: In the “General Options” dialog, you can set an Open password that protects the Excel workbook from opening and set a Modify password that protects the Excel workbook from editing.

How to show the Developer Tab in Excel

By default, the Developer Tab is not displayed as a menu option in Excel when you first open it up. You can, however, add it by following the few steps below.

Adding the Developer Tab to your Ribbon menu allows you to:

Write macros and VBA (Visual Basic for Applications) code.
Run macros that you previously recorded.
Use XML commands.
Use ActiveX controls.
Create applications to use with Microsoft Office programs.
Use form controls in Microsoft Excel.

Step 1 Click the File tab and then click Options



Step 2 Click Customize Ribbon.

Step 3 Under Customize the Ribbon and under Main Tabs, select the Developer check box and then click OK.



The Developer Tab will now show in the Ribbon Menu for you and will continue to show in Excel until you either reverse the instructions below or you reinstall Excel on your computer.

Excel : How to unprotect a password protected worksheet

In the case of a password protected worksheet where you are unable to Edit the data you can try the following steps to unprotect your worksheet.

Step 1: Press ALT +  F11 or click View Code on the Developers Tab

Step 2: Double click on the worksheet that is password protected.

Step 3: Copy and paste the code below into the (Code) window. Do not change any of the code, just copy and paste it.

Sub PasswordBreaker()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

Step 4: Click on the Run Button or press F5.

Step 5: It will return you back to the worksheet and an alert box will appear with one usable password. Click OK and the Excel sheet will be unprotected immediately. You can modify the sheet without password.

What is the use of Trim formula?

Purpose              : Removes un-necessary spaces in a given text

Return value     : Text with extra spaces removed.

Syntax                 : =TRIM (text)

Parameter list   :  text - The text from which to remove extra space.

Example:

 Image 1a

 Image 1b

=TRIM(A1)  [Formula]

Cell A1 contains the string:

="James"&CHAR(32)&CHAR(32)&"Bond"&CHAR(160)&CHAR(160)&"007". 

Note that there are 2 spaces between " James" and "Bond" and then 2 spaces (nonbreaking space characters with decimal value of 160) between "Bond" and "007". The string Length - LEN(A1) - is 16 characters. Refer Image 1a.

Formula returns the string "James Bond  007". TRIM Function deletes one space between "James" and "Bond". Length of returned string is 15 characters. TRIM function does not remove the nonbreaking spaces represented by CHAR(160), which can be done by using the SUBSTITUTE function as shown below. Refer Image 1b.

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))  [Formula]

Replaces each nonbreaking space character (Unicode value of 160) with a space character (ASCII value of 32) by using the SUBSTITUTE function, and then removes the multiple embedded spaces from the above string in Cell A1 (Image 1a).

Formula returns the string "James Bond 007". Length of returned string is 14 characters. Refer Image 2.

LOOKUP Function

Syntax : LOOKUP(lookup_value,  lookup_vector,  [result_vector])

The LOOKUP function vector form syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Important   The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.

What is the use of Length formula?

Purpose              :   Get the length of text.

Return value     :   A number representing the lengh of the text.

Syntax                 :   =LEN (text)

Parameter list   :   text - The text for which to calculate length.

Examples of Excel Len formula:

len("Shahul Hameed") = 13

len(A1) = length of the value in cell A1

To get the length of a string, use the LEN function.

Adding a Missing Closing Bracket

Use a formula to add any missing brackets. The following is just one example of the type of formula you can use:

=IF(AND(NOT(ISERROR(SEARCH("[",A1))),NOT(RIGHT(A1,1)="]")),A1&"]",A1)

The trick is to check to see if the cell (A1 in this case) has a left bracket in it and, if it does, check for the right bracket. If the right bracket isn't found, then you append one to the contents of the cell. Here's another variation on the same formulaic theme:

=IF(ISERROR(FIND("[",A1)),A1,IF(ISERROR(FIND("]",A1)),A1&"]",A1))

If you have to check large numbers of cells for missing brackets on a regular basis, you may want to create a macro that will examine a range of cells and add a right bracket if one is needed. Here's an example of how such a macro could be formulated:

Sub Close_Bracket()

    Dim c As Range

    Const csLBrk As String = "["

    Const csRBrk As String = "]"

    On Error Resume Next

    For Each c In Selection.Cells

        If InStr(1, c.Value, csLBrk) > 0 And _

          InStr(1, c.Value, csRBrk) = 0 Then

            c.Value = c.Value & csRBrk

        End If

    Next c

End Sub

To use the macro, simply select the range of cells you want to affect, and then run it. The cells are examined in-place and modified, if needed.