Macro security settings in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office administrator responsible for managing Excel files with macros.

📋 Request: Your manager wants you to ensure that all Excel files with macros are safe to use and that users are protected from potentially harmful macros.

📊 Data: You have a list of Excel files with information about whether they contain macros and their source trust level.

🎯 Deliverable: Create a simple guide and checklist in Excel to help users set their macro security settings correctly and identify safe files.

Sample Data

File Name	Contains Macro	Source
Report_Jan.xlsm	Yes	Trusted
Data_Export.xlsm	Yes	Unknown
Summary_Feb.xlsx	No	Trusted
Invoice_March.xlsm	Yes	Trusted
Budget_April.xlsx	No	Unknown
Analysis_May.xlsm	Yes	Unknown

1

Step 1: Create a new Excel sheet named 'Macro Security Guide'.

No formula needed.

Expected Result

A blank sheet named 'Macro Security Guide' is ready for instructions.

2

Step 2: In 'Macro Security Guide', write a short note explaining macro security levels: Disable all macros, Disable with notification, Enable all macros, and Trusted locations.

No formula needed.

Expected Result

Users can read about macro security options in the guide.

3

Step 3: Create a checklist table with columns: 'Step', 'Action', 'Status'. Fill 'Step' and 'Action' with steps to check macro settings and file trustworthiness.

No formula needed.

Expected Result

Checklist table is ready for users to mark 'Status' as Done or Pending.

4

Step 4: On the data sheet, add a new column 'Safe to Enable Macros' with formula to check if 'Contains Macro' is 'Yes' and 'Source' is 'Trusted'.

=IF(AND(B2="Yes", C2="Trusted"), "Yes", "No")

Expected Result

Column shows 'Yes' for files with macros from trusted sources, 'No' otherwise.

5

Step 5: Apply filter on the data table to show only files where 'Safe to Enable Macros' is 'Yes'.

Use Excel filter feature on the 'Safe to Enable Macros' column.

Expected Result

Filtered list shows only files safe to enable macros.

6

Step 6: Add conditional formatting to highlight files with 'Safe to Enable Macros' = 'No' in red.

Use conditional formatting rule: Formula =($D2="No") with red fill.

Expected Result

Unsafe files are visually highlighted in red.

7

Step 7: Save the workbook with macro security guide and checklist for users to follow.

No formula needed.

Expected Result

Workbook is saved and ready to distribute.

Final Result

Macro Security Guide
---------------------
Step | Action                          | Status
1    | Check macro security settings   | 
2    | Verify file source trust level  | 
3    | Enable macros only if safe      | 

Files Safe to Enable Macros:
File Name          | Contains Macro | Source  | Safe to Enable Macros
-------------------|----------------|---------|---------------------
Report_Jan.xlsm    | Yes            | Trusted | Yes
Invoice_March.xlsm | Yes            | Trusted | Yes

Files Not Safe:
Data_Export.xlsm   | Yes            | Unknown | No
Analysis_May.xlsm  | Yes            | Unknown | No

✓Only files with macros from trusted sources should have macros enabled.

✓Files from unknown sources with macros are highlighted as unsafe.

✓Users should set macro security to 'Disable with notification' to review before enabling.

✓A checklist helps users follow safe macro practices.

Bonus Challenge

Create a macro that automatically scans all files in a folder and updates the 'Safe to Enable Macros' column based on file properties.

Show Hint

Use VBA to loop through files, check if they contain macros, and check source trust level if possible.