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
- Open the Excel file you want to protect.
- Go to File > Info.
- Select 'Protect Workbook' > Encrypt with Password.
- Enter a Password in the dialog box. Click OK.
- Re-enter the password to confirm it. Click OK.
- 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
- Open the Excel file.
- Go to Review > Protect Workbook.
- Select the options you want to protect (e.g., Structure, Windows).
- Enter a Password if you want to restrict users from disabling the protection. Click OK.
- 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
- Open the Excel file.
- Select the sheet you want to protect.
- Go to Review > Protect Sheet.
- Enter a Password if you want to restrict users. Set Permissions (e.g., Select Locked Cells, Select Unlocked Cells).
- Click OK.
- Re-enter the password to confirm it. Click OK.
Excel > Review > Protect Sheet > Enter Password > Set Permissions > Re-enter Password
Step 4: Setting Permissions
- Right-click on the workbook or sheet.
- Select 'Protect Workbook' or 'Protect Sheet'.
- 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
- Open your Excel file.
- Navigate to the worksheet you want to protect.
' Protecting the worksheet
Sub ProtectWorksheet()
ActiveSheet.Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub
- 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
- Open your Excel file.
- Navigate to the workbook you want to protect.
' Protecting the workbook structure
Sub ProtectWorkbook()
ThisWorkbook.Protect Password:="YourWorkbookPassword", Structure:=True
End Sub
- 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
- Select "Save As" from the File menu.
- Click "Tools" at the bottom of the "Save As" dialog, then select "General Options".
- Enter a Password under the "Password to modify" field.
- Save the Workbook.
Unprotecting a Workbook with Read-Only Option
- Open the Workbook.
- 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:
-
Create a Sheet with Sensitive Data
Assume you have a sheet named
DataSheet
with sensitive information in columnA
. -
Prepare the Encryption Key
Choose a simple encryption key. For this example, we will use a numeric shift (Caesar Cipher):
Key: 3
-
Write the Encryption Formula
Use Excel's
CHAR
andCODE
functions to shift characters.=CHAR(CODE(A1) + 3)
-
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. -
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.
-
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
- Open the Excel file.
- Navigate to the
Review
tab. - Click
Protect Workbook
. - From the dropdown, choose
Protect Workbook Structure
. - 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
- Go to the
Review
tab. - Click
Allow Users to Edit Ranges
. - Click
New
to add a new range. - Define the range of cells and set a password.
- 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
- Save and close the workbook.
- Reopen the workbook.
- Navigate to the
Review
tab. - Click
Share Workbook
. - Ensure
Allow changes by more than one user at the same time
is checked. - 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)
- Save the Excel file to a OneDrive or SharePoint location.
- Right-click the file and select
Share
. - Enter the email addresses of the users.
- Choose
Can Edit
orCan View
based on permissions. - 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
- Use the
Review
tab forTrack Changes
->Highlight Changes
. - 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.