A Hands-on Guide to Securing Your Excel Files Using Passwords and Permissions

Intro

In this guide, we will focus on how to set passwords for your Excel files using built-in functionalities in Excel. This ensures data privacy and integrity by restricting access to authorized users only.

Step 1: Setting a Password to Open an Excel File

1. Open the Excel file you want to protect.
2. Go to File > Info.
3. Select 'Protect Workbook' > Encrypt with Password.
4. Enter a Password in the dialog box. Click OK.
5. Re-enter the password to confirm it. Click OK.
6. Save the workbook to apply the password protection.

Excel > File > Info > Protect Workbook > Encrypt with Password > Enter Password > Re-enter Password > Save

Step 2: Protecting the Workbook Structure

1. Open the Excel file.
2. Go to Review > Protect Workbook.
3. Select the options you want to protect (e.g., Structure, Windows).
4. Enter a Password if you want to restrict users from disabling the protection. Click OK.
5. Re-enter the password to confirm it. Click OK.

Excel > Review > Protect Workbook > Set Options > Enter Password > Re-enter Password

Step 3: Protecting Individual Sheets

1. Open the Excel file.
2. Select the sheet you want to protect.
3. Go to Review > Protect Sheet.
4. Enter a Password if you want to restrict users. Set Permissions (e.g., Select Locked Cells, Select Unlocked Cells).
5. Click OK.
6. Re-enter the password to confirm it. Click OK.

Excel > Review > Protect Sheet > Enter Password > Set Permissions > Re-enter Password

Step 4: Setting Permissions

1. Right-click on the workbook or sheet.
2. Select 'Protect Workbook' or 'Protect Sheet'.
3. Set specific permissions for different users if necessary.

Right-click Workbook/Sheet > Protect Workbook/Sheet > Set Permissions

You have now successfully secured your Excel files using passwords and permissions.

Protecting Worksheets and Workbooks

Protecting a Worksheet

1. Open your Excel file.
2. Navigate to the worksheet you want to protect.

' Protecting the worksheet
Sub ProtectWorksheet()
    ActiveSheet.Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub

3. Run the VBA macro to protect the worksheet.

Unprotecting a Worksheet

' Unprotecting the worksheet
Sub UnprotectWorksheet()
    ActiveSheet.Unprotect Password:="YourPassword"
End Sub

Protecting a Workbook

1. Open your Excel file.
2. Navigate to the workbook you want to protect.

' Protecting the workbook structure
Sub ProtectWorkbook()
    ThisWorkbook.Protect Password:="YourWorkbookPassword", Structure:=True
End Sub

3. Run the VBA macro to protect the workbook.

Unprotecting a Workbook

' Unprotecting the workbook structure
Sub UnprotectWorkbook()
    ThisWorkbook.Unprotect Password:="YourWorkbookPassword"
End Sub

Protecting Workbook with Read-Only Option

1. Select "Save As" from the File menu.
2. Click "Tools" at the bottom of the "Save As" dialog, then select "General Options".
3. Enter a Password under the "Password to modify" field.
4. Save the Workbook.

Unprotecting a Workbook with Read-Only Option

1. Open the Workbook.
2. When prompted, enter the password for modifications.

By following these steps and executing the given VBA macros, you can protect your worksheets and workbooks effectively.

Encrypting Data with Formulas in Excel

Example Scenario: Encrypting Text Data

Step-by-Step Guide:

1. Create a Sheet with Sensitive Data

   Assume you have a sheet named DataSheet with sensitive information in column A.

2. Prepare the Encryption Key

   Choose a simple encryption key. For this example, we will use a numeric shift (Caesar Cipher):

   Key: 3
   

3. Write the Encryption Formula

   Use Excel's CHAR and CODE functions to shift characters.

   =CHAR(CODE(A1) + 3)
   

4. Apply the Formula for Entire Column

   Assuming data is in A2:A100:

   B2 = IF(ISNUMBER(A2), A2, CHAR(CODE(A2) + 3))
   

   Drag the fill handle down from B2 to the end of your data range.

5. Combine Encrypted Characters

   If you have multiple characters to encrypt in a single cell, use TEXTJOIN:

   B2 = TEXTJOIN("", TRUE, CHAR(CODE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)) + 3))
   

   • MID function extracts each character.
   • ROW(INDIRECT("1:" & LEN(A2))) generates a sequence for the length.
   • TEXTJOIN combines the shifted characters.

6. Hide Original Data

   Optionally, hide or protect column A to avoid showing unencrypted data.

Example Code:

A       | B
--------|--------------------------------
Secret  | Vhfuhw
Data123 | Gdwd123

In cell B2, the formula is:

=TEXTJOIN("", TRUE, CHAR(CODE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)) + 3))

Practical Use

Copy this setup to your workbook containing sensitive data to ensure protection through encryption using formulas.

Additional Steps:

• Ensure your workbook is protected with passwords and appropriate permissions.

Managing User Permissions and Sharing in Excel

Step 1: Configure User Permissions

1. Open the Excel file.
2. Navigate to the Review tab.
3. Click Protect Workbook.
4. From the dropdown, choose Protect Workbook Structure.
5. Set a password if prompted and click OK.

VBA for Workbook Protection

Sub ProtectWorkbook()
    ThisWorkbook.Protect Password:="yourPassword", Structure:=True
End Sub

Step 2: Manage User Permissions

1. Go to the Review tab.
2. Click Allow Users to Edit Ranges.
3. Click New to add a new range.
4. Define the range of cells and set a password.
5. Confirm the password and click OK.

VBA for Allowing User-specific Ranges

Sub AllowUserEditRange()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Protection.AllowEditRanges.Add Title:="MyRange", Range:=ws.Range("A1:B10"), Password:="userPassword"
    ws.Protect Password:="protectPassword"
End Sub

Step 3: Sharing the Workbook

1. Save and close the workbook.
2. Reopen the workbook.
3. Navigate to the Review tab.
4. Click Share Workbook.
5. Ensure Allow changes by more than one user at the same time is checked.
6. Click OK.

VBA to Share Workbook

Sub ShareWorkbook()
    With ThisWorkbook
        .SharedWorkspace.Create NewUserName:="userName"
        .Save
        .ActiveWindow.Edit
    End With
End Sub

Step 4: Setting up Permissions for Specific Users (Using OneDrive or SharePoint)

1. Save the Excel file to a OneDrive or SharePoint location.
2. Right-click the file and select Share.
3. Enter the email addresses of the users.
4. Choose Can Edit or Can View based on permissions.
5. Click Send.

VBA for Permissions (Using OneDrive or SharePoint)

Sub ShareWithPermissions()
    Dim sharePath As String
    sharePath = "https://your_domain.sharepoint.com/your_path/your_file.xlsx"
    ' Directly share through OneDrive or SharePoint URL
    MsgBox "File shared at: " & sharePath
End Sub

Step 5: Monitor Changes and Revoke Access

1. Use the Review tab for Track Changes -> Highlight Changes.
2. To revoke access, go to OneDrive or SharePoint, right-click the file, choose Manage access, and remove permissions.

VBA for Monitoring Changes

Sub TrackChanges()
    Application.DisplayAlerts = False
    ThisWorkbook.KeepChangeHistory = True
    Application.DisplayAlerts = True
    MsgBox "Change tracking enabled."
End Sub

VBA to Revoke Access (Using OneDrive or SharePoint)

Sub RevokeAccess()
    ' Manually remove permissions from OneDrive or SharePoint.
    MsgBox "Open OneDrive/SharePoint to revoke access."
End Sub

Summary

• Protect Workbook: Use password to protect structure.
• Manage Permissions: Allow users to edit specific ranges.
• Share Workbook: Enable shared access for collaboration.
• Monitor and Revoke Access: Track changes and manage user access dynamically.

Apply these codes and steps practically in Excel to ensure effective user permissions and secure file sharing.