Sheet protection in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to protect the active sheet in Excel using VBA.

Excel

ActiveSheet.[1] "mypassword"

Drag options to blanks, or click blank then click option'

AProtect

BUnprotect

CSave

DOpen

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Unprotect' instead of 'Protect'.

Forgetting to add the password.

Using methods unrelated to protection like 'Save'.

The Protect method locks the active sheet with the given password.

2fill in blank

medium

Complete the code to unprotect the active sheet with a password.

Excel

ActiveSheet.[1] "mypassword"

Drag options to blanks, or click blank then click option'

AClose

BProtect

CSave

DUnprotect

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Protect' instead of 'Unprotect'.

Not providing the password.

Using unrelated methods like 'Save'.

The Unprotect method removes protection from the active sheet using the password.

3fill in blank

hard

Fix the error in the code to protect the sheet with password '1234'.

Excel

Worksheets("Sheet1").[1] "1234"

Drag options to blanks, or click blank then click option'

ASave

BUnprotect

CProtect

DClose

Attempts:

3 left

💡 Hint

Common Mistakes

Using password without quotes causing syntax error.

Using 'Unprotect' instead of 'Protect'.

The Protect method requires the password as a string, so it should be in quotes.

4fill in blank

hard

Fill both blanks to protect the sheet and allow users to select locked cells.

Excel

ActiveSheet.[1] Password:="pass", AllowSelectingLockedCells:=[2]

Drag options to blanks, or click blank then click option'

AProtect

BUnprotect

CTrue

DFalse

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Unprotect' instead of 'Protect'.

Setting AllowSelectingLockedCells to False disables selection.

Use Protect to lock the sheet and set AllowSelectingLockedCells to True to allow selection.

5fill in blank

hard

Fill all three blanks to unprotect the sheet, clear contents of cell A1, and then protect it again with a password.

Excel

ActiveSheet.[1] Password:="secret"
Range("A1").[2] = ""
ActiveSheet.[3] Password:="secret"

Drag options to blanks, or click blank then click option'

AProtect

BValue

CUnprotect

DClearContents

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Protect' instead of 'Unprotect' first.

Using 'ClearContents' as a method instead of setting Value to empty string.

Forgetting to protect the sheet again.

First, Unprotect unlocks the sheet. Then, setting Value of cell A1 to empty clears it. Finally, Protect locks the sheet again.

Practice

1. What is the main purpose of sheet protection in Excel?

easy

A. To change the font style of cells

B. To delete all data in the sheet

C. To automatically save the file

D. To prevent unwanted changes to the spreadsheet

Solution

1. Step 1: Understand sheet protection purpose

   Sheet protection is used to stop users from making unwanted changes to the sheet.

2. Step 2: Compare options

   Only To prevent unwanted changes to the spreadsheet correctly describes this purpose; others describe unrelated actions.

3. Final Answer:

   To prevent unwanted changes to the spreadsheet -> Option D

4. Quick Check:

   Sheet protection = prevent unwanted changes [OK]

Hint: Sheet protection stops edits except where allowed [OK]

Common Mistakes:

• Thinking it deletes data
• Confusing with autosave
• Assuming it changes formatting automatically

2. Which of the following is the correct way to unlock cells before protecting a sheet?

easy

A. Select cells, go to Data tab, click Sort

B. Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked'

C. Select cells, press Delete key

D. Select cells, change font color to white

Solution

1. Step 1: Identify how to unlock cells

   Unlocking cells is done via Format Cells > Protection tab by unchecking 'Locked'.

2. Step 2: Verify options

   Only Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' describes this correct method; others do unrelated actions.

3. Final Answer:

   Select cells, right-click, Format Cells, Protection tab, uncheck 'Locked' -> Option B

4. Quick Check:

   Unlock cells via Format Cells > Protection [OK]

Hint: Unlock cells via Format Cells before protecting sheet [OK]

Common Mistakes:

• Deleting cells instead of unlocking
• Using Data tab options wrongly
• Changing font color to hide data

3. You protect a sheet but forget to unlock cells where users enter data. What happens when users try to type in those cells?

medium

A. The sheet automatically unlocks those cells

B. Users can type normally without restrictions

C. Users get an error and cannot edit those cells

D. Users can only change font color in those cells

Solution

1. Step 1: Understand locked cells effect

   If cells are locked and sheet is protected, users cannot edit those cells.

2. Step 2: Analyze user experience

   Users will get an error message or be blocked from typing in locked cells.

3. Final Answer:

   Users get an error and cannot edit those cells -> Option C

4. Quick Check:

   Locked cells + protected sheet = no editing [OK]

Hint: Locked cells block editing when sheet is protected [OK]

Common Mistakes:

• Assuming sheet unlocks cells automatically
• Thinking users can edit anyway
• Believing only formatting is blocked

4. You protected a sheet but users report they cannot sort data. What is the likely cause and fix?

medium

A. Sheet protection disables sorting; allow sorting in protection options

B. Cells are unlocked; lock them to enable sorting

C. File is corrupted; repair the file

D. Sorting is disabled in Excel options; enable it there

Solution

1. Step 1: Identify sorting restriction in protected sheets

   By default, sheet protection disables sorting unless allowed in options.

2. Step 2: Fix by enabling sorting permission

   When protecting the sheet, check 'Sort' permission to allow users to sort data.

3. Final Answer:

   Sheet protection disables sorting; allow sorting in protection options -> Option A

4. Quick Check:

   Allow sorting in protection settings to fix [OK]

Hint: Enable sorting permission when protecting sheet [OK]

Common Mistakes:

• Locking cells to fix sorting
• Assuming file corruption causes sorting issue
• Looking in Excel options instead of protection settings

5. You want users to enter data only in cells B2:B10 but protect the rest of the sheet. Which steps correctly achieve this?

hard

A. Unlock B2:B10 cells, then protect sheet with default settings

B. Lock B2:B10 cells, then protect sheet allowing editing everywhere

C. Protect sheet first, then unlock B2:B10 cells

D. Delete all other cells except B2:B10 before protecting

Solution

1. Step 1: Unlock only B2:B10 cells

   Select cells B2:B10, open Format Cells > Protection tab, uncheck 'Locked'.

2. Step 2: Protect the sheet with default settings

   Protecting the sheet now locks all other cells except unlocked B2:B10, allowing data entry only there.

3. Final Answer:

   Unlock B2:B10 cells, then protect sheet with default settings -> Option A

4. Quick Check:

   Unlock input cells before protecting sheet [OK]

Hint: Unlock input cells first, then protect sheet [OK]

Common Mistakes:

• Locking input cells instead of unlocking
• Protecting sheet before unlocking cells
• Deleting cells instead of unlocking