Protecting Top Row Only in a Worksheet

[Legacy 324929]

How can the top row of a worksheet be protected/locked? We have a status log being accessed by multiple people who need to be able to add/delete rows. People keep putting typos in the top row however, which contains our column titles - so I want to find a way to lock that down.

I have tried the following so far:

• FAIL - Locking the top row
  • Selecting entire contents of worksheet, then going to Format Cells and unchecking "Locked".
  • Selecting top row only, then going to Format Cells and checking "Locked".
  • Protecting Worksheet.
    • Checking both insert and delete rows as user enabled options.
  • Result...
    • Can insert rows, but receive protected error message when attempting to delete row.
    • At row insertion, by default "Locked" is checked under Format Cells - is there a way to change this default behavior?
• FAIL - Repeating above steps without top row being frozen (just in case).
  • Result...
    • At row insertion, by default "Locked" is still checked under Format Cells.

 

[revcanon]

It appears that this only happens when inserting a row directly beneath the header row. If it is a requirement that the user always insert the data on the top row, you could try this:

• Select entire contents of worksheet, then go to Format Cells and uncheck "Locked".
• Insert a blank row between rows 1 and 2
• Select top row only, then go to Format Cells and checking "Locked".
• Hide Row 2
• Protect Worksheet.
  • Check both insert and delete rows as user enabled options.

That should result in a line that is always hidden, but has the unlocked property allowing the row beneath it to insert as unlocked.

 

[Legacy 324929]

You have my hearty thanks, Revcanon.

While your solution did not work, it did prompt me into a new wave of troubleshooting that let me isolate the problem - table formatting!!!

Our status log had table formatting applied to achieve alternating row colors, which was forcing the top row to be treated differently (or permanently reverting back to locked state, despite manual changes under Format Cells).

Here was the solution:

• Remove existing table formatting:
  • Unprotect sheet.
  • Right-click on any cell in table and go to Table | Convert to Range.
• Remove existing locked values:
  • Select entire contents of worksheet.
  • Right-click on any cell and go to Format Cells and uncheck "Locked" value.
• Reapply table formatting (with custom settings):
  • Go to Format as Table, and select any choice that only has alternating row colors (do not pick one with a darker header line -- alternating colors won't work properly when adding new rows later).
  • Under Data for your table, specify it to begin at row 2.
  • Under My table has headers, keep it checked.
• Lock top row:
  • Select top row.
  • Right-click on any cell and go to Format Cells and check "Locked".
• Protect worksheet:
  • Checking both insert and delete rows as user enabled options.

These hellish steps will allow you to keep alternating row color and protect your header row from pesky typo-prone users.

 

[revcanon]

Glad it spurred you on to your solution.