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.