Workbook protection in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What happens when you protect a workbook in Excel?

When you protect a workbook in Excel, which of the following is true?

A You prevent users from opening the workbook without a password.

B You prevent users from saving changes to the workbook.

C You prevent users from editing cell contents in any worksheet.

D You prevent users from adding, moving, deleting, or renaming worksheets.

💡 Hint

Think about what workbook protection controls versus worksheet protection.

Protecting a workbook mainly controls structural changes like adding or deleting sheets. It does not block editing cells or opening the file.

📊 Formula Result

intermediate

What is the result of this VBA code for workbook protection?

Consider this VBA code run in Excel:

ActiveWorkbook.Protect Password:="mypassword", Structure:=True, Windows:=False

What does this code do?

A Removes protection from the workbook.

B Protects the workbook structure with password 'mypassword', but allows window resizing.

C Protects the workbook windows and structure with password 'mypassword'.

D Protects only the workbook windows with password 'mypassword'.

💡 Hint

Look at the parameters Structure and Windows carefully.

The code protects the workbook's structure (like adding or deleting sheets) with the password 'mypassword'. Windows protection is off, so window size can be changed.

❓ Function Choice

advanced

Which Excel feature allows you to protect workbook structure without VBA?

You want to prevent users from adding or deleting sheets but allow editing cells. Which Excel feature should you use?

A Protect Workbook (Structure)

B Protect Sheet

C Encrypt with Password

D Mark as Final

💡 Hint

Think about what controls sheet management versus cell editing.

Protect Workbook with Structure option restricts adding, deleting, or renaming sheets but does not block cell editing.

🎯 Scenario

advanced

You want to allow users to edit cells but not move or delete sheets. What should you do?

You have a workbook with multiple sheets. You want users to freely edit cells but prevent them from moving, deleting, or renaming sheets. What is the best way to do this?

A Protect the workbook structure with a password and leave sheets unprotected.

B Protect each sheet with a password and leave workbook unprotected.

C Encrypt the workbook with a password to open.

D Mark the workbook as final.

💡 Hint

Think about which protection controls sheet management and which controls cell editing.

Protecting the workbook structure prevents moving or deleting sheets but does not block cell editing if sheets are unprotected.

📊 Formula Result

expert

What error occurs if you try to protect a workbook with an empty password in VBA?

What happens if you run this VBA code?

ActiveWorkbook.Protect Password:="", Structure:=True

A The workbook protection is ignored and remains unprotected.

B A runtime error occurs because password cannot be empty.

C The workbook is protected with no password, allowing anyone to unprotect it easily.

D Excel prompts the user to enter a password.

💡 Hint

Think about how Excel treats empty strings as passwords.

Using an empty string as password sets protection without a password, so anyone can unprotect easily.

Practice

1. What does protecting the workbook structure in Excel prevent users from doing?

easy

A. Adding, deleting, or moving worksheets within the workbook

B. Editing cell contents in any worksheet

C. Changing the font style in cells

D. Saving the workbook with a different name

Solution

1. Step 1: Understand workbook structure protection

   Protecting the workbook structure stops users from changing the sheet layout, like adding or deleting sheets.

2. Step 2: Identify what is restricted

   It does not stop editing cell contents or formatting, only structural changes.

3. Final Answer:

   Adding, deleting, or moving worksheets within the workbook -> Option A

4. Quick Check:

   Workbook structure protection = restrict sheet changes [OK]

Hint: Structure protection stops sheet changes, not cell edits [OK]

Common Mistakes:

• Confusing structure protection with cell protection
• Thinking it stops formatting changes
• Believing it prevents saving or renaming

2. Which of the following is the correct way to protect a workbook's structure in Excel?

easy

A. File > Save As > Encrypt with Password

B. Go to Review tab > Protect Workbook > Check 'Structure' > Set password

C. Right-click a cell > Format Cells > Protection > Lock Cell

D. Go to Home tab > Format > Protect Sheet > Set password

Solution

1. Step 1: Locate workbook protection option

   Workbook structure protection is found under Review tab > Protect Workbook.

2. Step 2: Select 'Structure' and set password

   Check the 'Structure' box and optionally set a password to prevent changes.

3. Final Answer:

   Go to Review tab > Protect Workbook > Check 'Structure' > Set password -> Option B

4. Quick Check:

   Protect Workbook with Structure option = correct method [OK]

Hint: Use Review > Protect Workbook for structure protection [OK]

Common Mistakes:

• Confusing Protect Sheet with Protect Workbook
• Trying to lock cells instead of workbook structure
• Using Save As encryption for structure protection

3. If a workbook is protected with structure protection enabled, what happens when a user tries to insert a new worksheet?

medium

A. Excel asks for the password before adding the worksheet

B. The new worksheet is added without any warning

C. The worksheet is added but remains hidden

D. Excel shows an error and prevents adding the worksheet

Solution

1. Step 1: Understand effect of structure protection

   When structure protection is on, Excel blocks adding or deleting sheets.

2. Step 2: What Excel does on insert attempt

   Excel shows an error message and does not allow the new sheet to be added.

3. Final Answer:

   Excel shows an error and prevents adding the worksheet -> Option D

4. Quick Check:

   Structure protection blocks sheet insertion = error shown [OK]

Hint: Structure protection blocks sheet insert with error [OK]

Common Mistakes:

• Thinking Excel asks password on insert
• Assuming sheet is added hidden
• Believing new sheets can be added freely

4. You protected a workbook's structure with a password but forgot it. What is the best way to regain full access?

medium

A. Use a VBA macro to remove the protection

B. Try to unprotect the workbook without a password

C. Save the workbook as a new file to remove protection

D. Close and reopen the workbook to reset protection

Solution

1. Step 1: Understand workbook protection password effect

   Without the password, Excel won't let you unprotect the workbook normally.

2. Step 2: Use VBA macro to remove protection

   A VBA macro can be used to remove or bypass the protection if password is lost.

3. Final Answer:

   Use a VBA macro to remove the protection -> Option A

4. Quick Check:

   Lost password = VBA macro removal [OK]

Hint: Use VBA to remove forgotten workbook password [OK]

Common Mistakes:

• Trying to unprotect without password (fails)
• Saving as new file does not remove protection
• Reopening file does not reset protection

5. You want to share an Excel file but prevent users from adding, deleting, or renaming sheets, while still allowing them to edit cell data. What steps should you take?

hard

A. Encrypt the file with a password to open

B. Protect each sheet with a password and leave workbook unprotected

C. Protect the workbook structure with a password and leave sheets unprotected

D. Lock all cells and protect sheets with a password

Solution

1. Step 1: Identify protection needed for sheet structure

   Protecting the workbook structure stops adding, deleting, or renaming sheets.

2. Step 2: Allow editing cell data by not protecting sheets

   Leaving sheets unprotected lets users edit cell contents freely.

3. Final Answer:

   Protect the workbook structure with a password and leave sheets unprotected -> Option C

4. Quick Check:

   Structure protection + unprotected sheets = desired control [OK]

Hint: Protect workbook structure only to restrict sheets, allow edits [OK]

Common Mistakes:

• Protecting sheets blocks cell editing
• Encrypting file restricts opening, not sheet changes
• Locking cells and protecting sheets restricts editing